需要帮助以最少的联接来优化MySQL查询 [英] Need Help for Optimizing MySQL query with few joins
问题描述
您好，我有以下sql查询，它可以正常工作，但是有点慢
SELECT
ep。 *，
t_u.sUsername AS sLockedBy，
epi.sName，
em.sName AS sManufacturerName，
SUM（IF（eop.dInProduction为NULL
和eop。 dFromProduction是NULL
和eop.dShipped是NULL
和eop.nIsCancelled = 0
和eos.sStatusCode NOT IN（'Canceled'，'Finished'，'Complete'），
1，
0））AS nProductOrdered，
SUM（IF（eop.dInProduction不为空
并且eop.dFromProduction为空
和eop.dShipped为空
AND eop.nIsCancelled = 0
AND eos.sStatusCode NOT IN（'Canceled'，'Finished'，'Complete'，
1，
0））AS nProductInProduction，
SUM（IF（eop.dInProduction不为空
和eop.dFromProduction不为空
和eop.dShipped为空
和eop.nIsCancelled = 0
和eos.sStatusCode不输入（取消，完成，完成），
1，
0））AS nProductFromProduction，
SUM（IF（eop.dInProduction IS NULL
AND eop.dFromProduction IS NULL
和eop.dShipped IS NULL
AND eop.nIsCancelled = 0
AND eos.sStatusCode不输入（'取消'，'完成'，'完成'，
1，
0））+ ep.nInStock AS nStockCheck
从
eshop3_products作为ep
左加入
用户作为t_u在ep.nUserLockID = t_u.nUID
和t_u.nDeleted = 0
内部加入
eshop3_products_i18n AS Epi on ep.nUID = epi.nProductID
AND epi.nLangID = 1
AND epi.nDeleted = 0
LEFT JOIN
eshop3_manufacturers AS em on ep.nManufacturerID = em.nUID
和em.nDeleted = 0
左联接
eshop3_order_products AS eop ON ep.nUID = eop.nProductID
左联接
eshop3_orders AS eo ON eop .nOrderID = eo.nUID
左加入
eshop3_order_statuses作为eos在eo.nUID = eos.nOrderID
和eos.nUID =（从
eshop3_order_statuses中选择
nUID
作为eos
WHERE
eo。 nUID = eos.nOrderID
由eos.nUID DESC
限制1）
WHERE
（ep.nDeleted = 0）
GROUP BY ep.nUID
ORDER BY ep.sStoreCode ASC
产品表共有462行，
订单状态表共有17 154行
查询的执行时间为1.5秒，我认为这很慢。 / p>
我在ON子句中使用INNER SELECT来获取给定订单的最后状态，在这里我认为有问题。
然后我从整个查询中删除订单状态表，执行时间快了0.152秒几倍
能否给我一些建议我优化了查询
她e您使用带说明的SQL
id select_type表的类型possible_keys key key_len ref行Extra
1 PRIMARY epi ALL nProductID 408使用位置；使用临时；使用filesort
1 PRIMARY ep eq_ref PRIMARY，nUID PRIMARY 4 dragoni_zfms.epi.nProductID 1使用where
1 PRIMARY em eq_ref PRIMARY PRIMARY 4 dragoni_zfms.ep.nManufacturerID 1
1 PRIMARY t_u eq_ref PRIMARY 4 dragoni_zfms.ep.nUserLockID 1
1 PRIMARY eop ref id_productid id_productid 5 dragoni_zfms.ep.nUID 13
1 Peoary eo eq_ref PRIMARY PRIMARY 4 dragoni_zfms.eop.nOrderID 1使用索引
1 PRIMARY eos eq_ref PRIMARY，nOrderID PRIMARY 4 func 1
2依赖子查询eos ref nOrderID nOrderID 4 dragoni_zfms.eo.nUID 1使用where;使用索引；使用文件排序
在您的情况下，执行某些操作更容易操作不是使用mysql，而是使用php。
我在练习中看到的是赢得时间的机会。请尝试将一个复杂的查询分为几个简单的查询。然后使用php脚本执行必要的过滤。
希望能有所帮助。
谢谢！
Hello I have the follow sql query it works but it is kind of slow
SELECT
ep . *,
t_u.sUsername AS sLockedBy,
epi.sName,
em.sName AS sManufacturerName,
SUM(IF(eop.dInProduction IS NULL
AND eop.dFromProduction IS NULL
AND eop.dShipped IS NULL
AND eop.nIsCancelled = 0
AND eos.sStatusCode NOT IN ('Canceled' , 'Finished', 'Complete'),
1,
0)) AS nProductOrdered,
SUM(IF(eop.dInProduction IS NOT NULL
AND eop.dFromProduction IS NULL
AND eop.dShipped IS NULL
AND eop.nIsCancelled = 0
AND eos.sStatusCode NOT IN ('Canceled' , 'Finished', 'Complete'),
1,
0)) AS nProductInProduction,
SUM(IF(eop.dInProduction IS NOT NULL
AND eop.dFromProduction IS NOT NULL
AND eop.dShipped IS NULL
AND eop.nIsCancelled = 0
AND eos.sStatusCode NOT IN ('Canceled' , 'Finished', 'Complete'),
1,
0)) AS nProductFromProduction,
SUM(IF(eop.dInProduction IS NULL
AND eop.dFromProduction IS NULL
AND eop.dShipped IS NULL
AND eop.nIsCancelled = 0
AND eos.sStatusCode NOT IN ('Canceled' , 'Finished', 'Complete'),
1,
0)) + ep.nInStock AS nStockCheck
FROM
eshop3_products AS ep
LEFT JOIN
users AS t_u ON ep.nUserLockID = t_u.nUID
AND t_u.nDeleted = 0
INNER JOIN
eshop3_products_i18n AS epi ON ep.nUID = epi.nProductID
AND epi.nLangID = 1
AND epi.nDeleted = 0
LEFT JOIN
eshop3_manufacturers AS em ON ep.nManufacturerID = em.nUID
AND em.nDeleted = 0
LEFT JOIN
eshop3_order_products AS eop ON ep.nUID = eop.nProductID
LEFT JOIN
eshop3_orders AS eo ON eop.nOrderID = eo.nUID
LEFT JOIN
eshop3_order_statuses AS eos ON eo.nUID = eos.nOrderID
AND eos.nUID = (SELECT
nUID
FROM
eshop3_order_statuses as eos
WHERE
eo.nUID = eos.nOrderID
ORDER BY eos.nUID DESC
LIMIT 1)
WHERE
(ep.nDeleted = 0)
GROUP BY ep.nUID
ORDER BY ep.sStoreCode ASC
The Product Table has total 462 rows,
The Order Statuses Tables has total 17 154 rows
The execution time of the query is 1.5 secs which I think it is slow.
I use the INNER SELECT in the ON clause to get the last status for the given order, and here I think there is something wrong.
WHen I remove the order statuses table from the whole query the execution time is several times faster 0.152 secs
Could you give me some advice how can I optimize the query I have
Here you go the sql with explain
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY epi ALL nProductID 408 Using where; Using temporary; Using filesort
1 PRIMARY ep eq_ref PRIMARY,nUID PRIMARY 4 dragoni_zfms.epi.nProductID 1 Using where
1 PRIMARY em eq_ref PRIMARY PRIMARY 4 dragoni_zfms.ep.nManufacturerID 1
1 PRIMARY t_u eq_ref PRIMARY PRIMARY 4 dragoni_zfms.ep.nUserLockID 1
1 PRIMARY eop ref id_productid id_productid 5 dragoni_zfms.ep.nUID 13
1 PRIMARY eo eq_ref PRIMARY PRIMARY 4 dragoni_zfms.eop.nOrderID 1 Using index
1 PRIMARY eos eq_ref PRIMARY,nOrderID PRIMARY 4 func 1
2 DEPENDENT SUBQUERY eos ref nOrderID nOrderID 4 dragoni_zfms.eo.nUID 1 Using where; Using index; Using filesort
In your case it is easier to perform some operations not using mysql but using php. I have seen in my practice cases when you win time. Please, try to divide a complex query into several simple queries. Then perform the necessary filtering using php scripts. Hope it helps. Thanks!
这篇关于需要帮助以最少的联接来优化MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
