Multiple Where OR's from Multiple Tables MySQL

Question

SELECT m.SKU, m.AltSKU, e.OrderLineItemID, e.SKU, e.Quantity, e.TransactionPrice, e.CreatedTime
FROM MasterSKU m, eBayOrders_store1 e
WHERE m.SKU = e.SKU  
AND 
m.AltSKU IS NOT NULL
AND 
e.CreatedTime BETWEEN '2017-03-16 00:00:00' AND '2017-03-16 23:59:59';

Queries very fast.

Query took 0.0105 sec.

But when I try to combine all the eBayOrder tables, as such:

SELECT m.SKU, m.AltSKU, e.OrderLineItemID, e.SKU, e.Quantity, e.TransactionPrice, e.CreatedTime
FROM MasterSKU m, eBayOrders_store1 e, eBayOrders_store2 z, eBayOrders_store3 d, eBayOrders_store4 c
WHERE (m.SKU = e.SKU) OR (m.SKU = z.SKU) OR (m.SKU = d.SKU) OR (m.SKU = c.SKU)  
AND 
m.AltSKU IS NOT NULL
AND 
e.CreatedTime BETWEEN '2017-03-16 00:00:00' AND '2017-03-16 23:59:59';

It basically froze out the MySQL connection. I checked the query and it was running trying to obtain data for over 10 minutes. I had to go into SSH and kill the process/query manually to stop it.

What is wrong?

`OR` is difficult for MySQL to optimize with indexes. Use `EXPLAIN` to see how it's doing this query. — Barmar, Mar 25 '17 at 16:03
Also, when you combine `AND` and `OR`, you should always add parentheses around the groups. See http://stackoverflow.com/questions/27663976/sql-statement-is-ignoring-where-parameter — Barmar, Mar 25 '17 at 16:03
It's usually better to split the query up into several queries that you combine with `UNION` instead of using `OR`. — Barmar, Mar 25 '17 at 16:04
*Never* use commas in the `FROM` clause. *Always* use explicit, proper `JOIN` syntax. — Gordon Linoff, Mar 25 '17 at 16:05
To me, looks like something you'd want to do with a bunch of LEFT JOINs against MasterSKU. I'd try to write the query if it was in SQLFiddle, but I'm not going to mock out your data for you. — erik258, Mar 25 '17 at 16:07
Updated my answer to ANSI-92 syntax, as per @GordonLinoff's comment — Steven Moseley, Mar 25 '17 at 16:14
@GordonLinoff I'm trying to research why you are saying to *never* use commas in the from clause. It seems to be very common to do. Can you elaborate at all? — bbruman, Mar 25 '17 at 16:37
@bbruman . . . That syntax was obsoleted over 20 years ago. Explicit `JOIN` syntax is more powerful because it supports outer joins. Most people also find it easier to follow the query, when the conditions are next to the table declarations. — Gordon Linoff, Mar 26 '17 at 22:09

score 2 · Answer 1 · edited Jan 06 '22 at 06:45

2

The problem is that you are doing a cross join of all the tables and then filtering out a few rows. If any conditions match between two tables, then you are getting each match with all the rows from all the other tables, cross joined.

I'm guessing that this query does not come close to what you want to do. It may generate a bunch of duplicates. Rows may be missing from the output. If an id is in only one table, it will not ever be in the output.

edited Jan 06 '22 at 06:45

Nimantha

6,405
6
28
69

answered Mar 25 '17 at 16:07

Gordon Linoff

1,242,037
58
646
786

Or just fix the existing question with an SQLFiddle mockup. – erik258 Mar 25 '17 at 16:08

Steven Moseley · Accepted Answer · 2017-03-25T16:13:48.723

Using UNIONs will speed things up, by containing each store table in its own subquery (preventing cross-joins between them).

The downside is redundancy in your query. But you can work around this by generating the union with code.

SELECT m.SKU, m.AltSKU, e.OrderLineItemID, e.SKU, e.Quantity, e.TransactionPrice, e.CreatedTime
FROM MasterSKU AS m
INNER JOIN eBayOrders_store1 AS e ON m.SKU = e.SKU  
    AND e.CreatedTime BETWEEN '2017-03-16 00:00:00' AND '2017-03-16 23:59:59'
WHERE m.AltSKU IS NOT NULL
UNION
SELECT m.SKU, m.AltSKU, e.OrderLineItemID, e.SKU, e.Quantity, e.TransactionPrice, e.CreatedTime
FROM MasterSKU AS m
INNER JOIN eBayOrders_store2 AS e ON m.SKU = e.SKU  
    AND e.CreatedTime BETWEEN '2017-03-16 00:00:00' AND '2017-03-16 23:59:59'
WHERE m.AltSKU IS NOT NULL
UNION
SELECT m.SKU, m.AltSKU, e.OrderLineItemID, e.SKU, e.Quantity, e.TransactionPrice, e.CreatedTime
FROM MasterSKU AS m
INNER JOIN eBayOrders_store3 AS e ON m.SKU = e.SKU  
    AND e.CreatedTime BETWEEN '2017-03-16 00:00:00' AND '2017-03-16 23:59:59'
WHERE m.AltSKU IS NOT NULL
UNION    
SELECT m.SKU, m.AltSKU, e.OrderLineItemID, e.SKU, e.Quantity, e.TransactionPrice, e.CreatedTime
FROM MasterSKU AS m
INNER JOIN eBayOrders_store4 AS e ON m.SKU = e.SKU  
    AND e.CreatedTime BETWEEN '2017-03-16 00:00:00' AND '2017-03-16 23:59:59'
WHERE m.AltSKU IS NOT NULL;

You could simplify this by putting the UNION into a derived table, but this may negatively impact performance when you lose the key on e.SKU on the join:

SELECT m.SKU, m.AltSKU, e.OrderLineItemID, e.SKU, e.Quantity, e.TransactionPrice, e.CreatedTime
FROM MasterSKU AS m
INNER JOIN (
    SELECT * FROM eBayOrders_store1 WHERE CreatedTime BETWEEN '2017-03-16 00:00:00' AND '2017-03-16 23:59:59'
    UNION SELECT * FROM eBayOrders_store2 WHERE CreatedTime BETWEEN '2017-03-16 00:00:00' AND '2017-03-16 23:59:59'
    UNION SELECT * FROM eBayOrders_store3 WHERE CreatedTime BETWEEN '2017-03-16 00:00:00' AND '2017-03-16 23:59:59'
    UNION SELECT * FROM eBayOrders_store4 WHERE CreatedTime BETWEEN '2017-03-16 00:00:00' AND '2017-03-16 23:59:59'
) AS e ON m.SKU = e.SKU  
WHERE m.AltSKU IS NOT NULL;

Really great; is what I was looking for. Same results for both queries of course, and both are fairly fast. First query: `0.0522 sec` `0.0657 sec` `0.0802 sec`, Second query: `0.1661 sec` `0.1735 sec` `0.1337 sec`. I'll have to look more into the differences. — bbruman, Mar 25 '17 at 16:23

Multiple Where OR's from Multiple Tables MySQL

2 Answers2