Search code examples
mysqlquery-performancesubquery

Update Query taking long to run

I need some assistance with a query I am using (MySQL) to update another table. When running the nested query, the query runs in less than a second. But as soon as I include the update part, it takes hours to run. The query I am using is as per the below:

UPDATE sys_reference.outlet_reference OUTREF LEFT JOIN
(SELECT 
    store_code 'storeCode'
,   LEFT(header_value,20) 'CoOrds' 
FROM 
    am_data_warehouse.am_headers 
WHERE 
    action_date = CURDATE()- 1 
AND header_field_id IN (3641, 4937)
) GPSCO 
ON OUTREF.store_code = GPSCO.storeCode 
SET OUTREF.gps_coordinates = GPSCO.CoOrds

Below is the structure of the table that is being updated:

MySQL Table

Solution

  • I think the sub-query isn't doing you any favors here. I think you can rewrite it to elimiate the sub-query.

    UPDATE
    sys_reference.outlet_reference AS OUTREF
    INNER JOIN am_data_warehouse.am_headers AS GPSCO ON OUTREF.store_code = 
GPSCO.storeCode
SET
    OUTREF.gps_coordinates = LEFT(GPSCO.header_value,20)
WHERE
    GPSCO.action_date = CURDATE() - 1
    AND GPSCO.header_field_id IN (3641, 4937)