Why is My MySQL CASE WHEN Update Slow and Affecting All Rows?

MySQL Update Using CASE WHEN/THEN/ELSE

Problem:

When attempting to update a large MyISAM table using a CASE WHEN/THEN/ELSE statement, the query monopolizes the CPU and takes an excessive amount of time to complete. Moreover, it updates all rows in the table, even those not specified in the query.

Explanation:

The reason for this behavior is that MySQL interprets the CASE statement as updating all rows in the table. Rows that do not match any of the WHEN conditions are assigned a NULL value.

Solution:

To avoid this issue, the query should be modified to include an ELSE statement that assigns the existing value to rows that do not match any of the WHEN conditions. Additionally, a WHERE clause can be used to limit the update to only the specified rows.

The corrected query:

UPDATE `table` SET `uid` = CASE
    WHEN id = 1 THEN 2952
    WHEN id = 2 THEN 4925
    WHEN id = 3 THEN 1592
    ELSE `uid`
    END
WHERE id IN (1, 2, 3);

The above is the detailed content of Why is My MySQL CASE WHEN Update Slow and Affecting All Rows?. For more information, please follow other related articles on the PHP Chinese website!