avoid subquery in mysql update statement

admin

6 years ago

公司最近有個deployment，要run個SQL patch。係QA 同staging 都冇事，點知一去production run左30mins 都未行完，terminated & rollback.

原來係其中一個update statement 行subquery，where id = (select …) mysql 會run select on each row 🙁 DataSet 一大，慢到嘔。

CREATE TABLE alarm_backup SELECT * FROM alarm;  

UPDATE alarm a
SET a.status = 'active' , ....... 
WHERE a.id = (
  SELECT al.id 
  FROM alarm_backup ab 
    JOIN device d ON ab.device_id = d.id
    JOIN ......
  WHERE .....
);

CREATE TABLE alarm_backup SELECT * FROM alarm;

UPDATE alarm a

SET a.status = 'active' , .......

WHERE a.id = (

SELECT al.id

FROM alarm_backup ab

JOIN device d ON ab.device_id = d.id

JOIN ......

WHERE .....

);

其實subquery 係唔需要⋯⋯

CREATE TABLE alarm_backup SELECT * FROM alarm;  

UPDATE alarm a
JOIN device d ON a.device_id = d.id
JOIN ......
SET a.status = 'active' , ....... 
WHERE ..... ；

CREATE TABLE alarm_backup SELECT * FROM alarm;

UPDATE alarm a

JOIN device d ON a.device_id = d.id

JOIN ......

SET a.status = 'active' , .......

WHERE ..... ；

故事教訓我地：
1) 放假返黎之後小心點，你唔係到個時既Peer review 都要睇返。
2) 唔好咁容易信你上司，上左神枱技術係會回塘 😂