MySQL - 慢查询优化

Question

我是一个有用的助手，可以将文本翻译成中文。

我有一个优化非常慢的prestashop SQL查询的问题 (mysql需要约3.5秒才能得到结果)。

查询:

SELECT
    SQL_CALC_FOUND_ROWS a.*, a.id_order AS id_pdf, 
    CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `customer`, 
    osl.`name` AS `osname`, 
    os.`color`, 
    IF((SELECT COUNT(so.id_order) FROM `ps_orders` so WHERE so.id_customer = a.id_customer) > 1, 0, 1) as new, 
    (SELECT COUNT(od.`id_order`) FROM `ps_order_detail` od WHERE od.`id_order` = a.`id_order` GROUP BY `id_order`) AS product_number 
FROM `ps_orders` a 
LEFT JOIN `ps_customer` c 
    ON (c.`id_customer` = a.`id_customer`) 
LEFT JOIN `ps_order_history` oh 
    ON (oh.`id_order` = a.`id_order`) 
LEFT JOIN `ps_order_state` os 
    ON (os.`id_order_state` = oh.`id_order_state`) 
LEFT JOIN `ps_order_state_lang` osl 
    ON (os.`id_order_state` = osl.`id_order_state` AND osl.`id_lang` = 6) 
WHERE 1 
    AND oh.`id_order_history` = (SELECT MAX(`id_order_history`) FROM `ps_order_history` moh WHERE moh.`id_order` = a.`id_order` GROUP BY moh.`id_order`) 
ORDER BY `date_add` DESC
LIMIT 0,50

解释结果：

我应该做什么？

提前感谢您的答案。

- user1768501

---

你有任何索引吗？ - max

是的，有创建的索引（默认由prestashop创建）。 - mstraczkowski

内部查询的时间是多少，从ps_order_history表中选择最大的id_order_history，其中moh.id_order = a.id_order，按照moh.id_order进行分组。你是否创建了适当的索引，即用于where比较的列？ - Anil

上述查询速度很快（约为0.1005秒），我认为索引是正确的。 - mstraczkowski

尝试在删除IF((SELECT COUNT(so.id_order) FROM ps_orders so WHERE so.id_customer = a.id_customer) > 1, 0, 1) as new后运行，(SELECT COUNT(od.id_order) FROM ps_order_detail od WHERE od.id_order = a.id_order GROUP BY id_order) AS product_number，这些是否会影响性能。我还有一个关于"ORDER BY date_add DESC"的问题，这个date_add属于哪个表。 - Anil

@Gordon Linoff 已经发现了 SQL_CALC_FOUND_ROWS 的问题 - 感谢您的帮助。 - mstraczkowski

Answer 1

这段内容略微有些冗长。

explain 看起来是合理的，但是最后的 order by 要求在 204,480 行上进行 filesort，这可能占用了大部分时间。不清楚列 date_add 是从哪里来的。但是，也许你知道所有行都来自最近一周左右。如果是这样，那么一个额外的 where 子句可能会有所帮助：

where date_added >= now() - interval 7 day and . . .

- Gordon Linoff

---

当我删除了 ORDER BY 子句后，它仍然需要大约3秒钟的时间。 - mstraczkowski

当您移除SQL_CALC_FOUND_ROWS时会发生什么？这也明显地降低了查询速度。 - Gordon Linoff

哇，现在只需要约0.0116秒，所以我认为你已经找到了慢的原因，非常感谢，非常感激。 - mstraczkowski