Mysql – Optimize MySQL update statement

MySQLmysql-5.6optimizationupdate

I have an update query that selects data from a table (HST) and updates it into another table (PCL). The PCL table contains ~30000 rows while the HST table contains about 1.1 million rows. I have multiple (composite) indexes on HST and despite being a large table all queries on it are fairly quick (~2-3 sec). However, when I attempt to select rows from this table and update PCL, it takes 3-4 hours, and in most cases I get "Lock wait timeout exceeded; try restarting transaction" errors and I have to keep trying until it works. 

UPDATE PCL 
SET `T2A` = (SELECT HST.`Date` from `HST` WHERE HST.`SYM`=PCL.INST AND 
HST.`DATE` >=  PCL.Date AND HST.`HP` >= PCL.T2 order by HST.`Date` limit 1)    
WHERE `BS` = 'B' AND `T1A` IS NOT NULL;

Is there a way I can rewrite the above query so that it runs faster?

Best Answer

Think about something like

UPDATE PCL, ( SELECT PCL.SrNo, MIN(HST.Date) minDate
              FROM PCL, HST 
              WHERE HST.SYM=PCL.INST 
                AND HST.DATE >=  PCL.Date 
                AND HST.HP >= PCL.T2 
                AND PCL.BS = 'B' 
                AND PCL.T1A IS NOT NULL
              GROUP BY PCL.SrNo ) subquery
SET PCL.T2A = subquery.minDate
WHERE PCL.SrNo = subquery.SrNo;

Of course, proper indices on both tables is safe.

Related Question