Fury Fury - 2 months ago 7
MySQL Question

How to speedup my update query associated with subquery

I have a query which is pretty that contains

LEFT JOIN
subquery. It takes 20 minutes to load completely.

Here is my query:

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


Is there any way that I can make this query runs faster?

Answer

Move Where condition in Temp Table and replace WHERE with HAVING Clause, this will eliminate unnecessary rows from temp table so reduce the filtering and may help to improve performance

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