mysql - 如何加快与子查询关联的更新查询

我有一个很漂亮的查询，它包含LEFT JOIN子查询。完全加载需要20分钟。
我的问题是：

UPDATE orders AS o

LEFT JOIN (
    SELECT obe_order_master_id, COUNT(id) AS count_files, id, added
    FROM customer_instalments
    GROUP BY obe_order_master_id
) AS oci ON oci.obe_order_master_id = SUBSTRING(o.order_id, 4)

SET o.final_customer_file_id = oci.id,
    o.client_work_delivered  = oci.added

WHERE oci.count_files = 1

有什么方法可以让这个查询运行得更快吗？

最佳答案

在临时表中移动Where条件并用WHERE子句替换HAVING，这将消除临时表中不必要的行，从而减少筛选，并有助于提高性能

UPDATE orders AS o
LEFT JOIN (
   SELECT obe_order_master_id, id, added
   FROM customer_instalments
   GROUP BY obe_order_master_id
   HAVING COUNT(id) = 1
) AS oci ON oci.obe_order_master_id = SUBSTRING(o.order_id, 4)
SET o.final_customer_file_id = oci.id,
    o.client_work_delivered  = oci.added

关于mysql - 如何加快与子查询关联的更新查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/39658219/