Hello I have the follow sql query it works but it is kind of slow
您好我有以下sql查询它的工作原理,但它有点慢
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,
产品表共有462行,
The Order Statuses Tables has total 17 154 rows
订单状态表共有17 154行
The execution time of the query is 1.5 secs which I think it is slow.
查询的执行时间是1.5秒,我认为它很慢。
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.
我在ON子句中使用INNER SELECT来获取给定订单的最后状态,在这里我认为有些错误。
WHen I remove the order statuses table from the whole query the execution time is several times faster 0.152 secs
当我从整个查询中删除订单状态表时,执行时间比0.152秒快几倍
Could you give me some advice how can I optimize the query I have
你能给我一些建议我如何优化我的查询
Here you go the sql with explain
在这里你去解释sql
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
1 个解决方案
#1
0
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但使用php的操作。我在练习中看到了你赢得时间的情况。请尝试将复杂查询划分为几个简单查询。然后使用PHP脚本执行必要的过滤。希望能帮助到你。谢谢!
#1
0
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但使用php的操作。我在练习中看到了你赢得时间的情况。请尝试将复杂查询划分为几个简单查询。然后使用PHP脚本执行必要的过滤。希望能帮助到你。谢谢!