whoami whoami - 4 months ago 14
MySQL Question

MySQL update takes too long

We have more than 20 million records in a table and we tried to update 3K records but it took more than 7 mins and it didn't complete, so terminated the query.

Sample query,

UPDATE TABLE_A
SET STATUS = 'PENDING'
WHERE ID IN (
SELECT ID
FROM TMP_TABLE_A_STATUS_FIX
); /*took more than 7 mins and didn't complete even after that*/


We collected all the ids which needs to be updated in a temp table TMP_TABLE_A_STATUS_FIX(has only 3K records).

As the above query took too long, we updated individually like,

UPDATE TABLE_A SET STATUS = 'PENDING' WHERE ID = 1;
UPDATE TABLE_A SET STATUS = 'PENDING' WHERE ID = 2;
UPDATE TABLE_A SET STATUS = 'PENDING' WHERE ID = 3;
.
.
.
UPDATE TABLE_A SET STATUS = 'PENDING' WHERE ID = 2999;
UPDATE TABLE_A SET STATUS = 'PENDING' WHERE ID = 3000; /*updated all 3K recordds in 0.00 secs*/


I really do not understand what is wrong with the
IN
query.

Can someone explain what is wrong in the update which has
IN
in where clause and why it didn't complete even after 7 mins?

Note - ID in TABLE_A is primary key and its indexed. STATUS in TABLE_A is also indexed. We removed the index for STATUS as we thought updating the index column may take time due to index re-organization but that didn't help.

Answer

Insetad of using an in clause try with an inner join

UPDATE TABLE_A
INNER JOIN TMP_TABLE_A_STATUS_FIX on TABLE_A.ID = TMP_TABLE_A_STATUS_FIX .ID
SET STATUS = 'PENDING';