mysql - sql查询执行时间很长

Question

我尝试以良好的响应时间执行以下 SQL 查询，但是当我添加 ORDER BY 语句时，查询不会返回任何内容超过 5 分钟。

SELECT *  
FROM orders co
LEFT JOIN (SELECT * FROM prod_orders) AS pc
ON co.id_order = pc.id_order
LEFT JOIN (SELECT * FROM clients) AS cl
ON co.id_client = cl.id_client
LEFT JOIN (SELECT * FROM clients_address) AS ca
ON co.id_client = ca.id_client
LEFT JOIN (SELECT * FROM clienti_firme) AS cf
ON ca.id_client = cf.id_client
ORDER BY co.id_order 

Answer 1

如果 Order By 减慢了速度，请尝试:

SELECT *  
FROM (SELECT * FROM orders ORDER BY id_order) AS co
LEFT JOIN prod_orders AS pc
ON co.id_order = pc.id_order
LEFT JOIN clients AS cl
ON co.id_client = cl.id_client
LEFT JOIN clients_address AS ca
ON co.id_client = ca.id_client
LEFT JOIN clienti_firme AS cf
ON ca.id_client = cf.id_client
ORDER BY co.id_order 

也许所有派生表的执行计划都使其对第一个表(订单)的每一行运行 SELECT *。试试这个:

SELECT *  
FROM orders co
LEFT JOIN prod_orders AS pc
ON co.id_order = pc.id_order
LEFT JOIN clients AS cl
ON co.id_client = cl.id_client
LEFT JOIN clients_address AS ca
ON co.id_client = ca.id_client
LEFT JOIN clienti_firme AS cf
ON ca.id_client = cf.id_client
ORDER BY co.id_order