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';
查询速度非常快。
查询耗时 0.0105 秒。
但是当我尝试组合所有 eBayOrder 表时，如下所示：
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';
它基本上冻结了 MySQL 连接。我检查了查询，它正在运行试图获取数据超过 10 分钟。我不得不进入 SSH 并手动终止进程/查询以停止它。
怎么了？
使用UNIONs 将加快速度，通过在其自己的子查询中包含每个存储表（防止它们之间的交叉连接）。
缺点是查询中有冗余。但是您可以通过使用代码生成联合来解决这个问题。
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;
您可以通过将 UNION 放入派生表来简化此操作，但是当您在连接中丢失 e.SKU 上的键时，这可能会对性能产生负面影响：
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;
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句