8种你可能正在写错的SQL用法(2)

执行计划：

+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+  
| id | select_type        | table | type  | possible_keys | key     | key_len | ref   | rows | Extra                                               |  
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+  
| 1  | PRIMARY            | o     | index |               | PRIMARY | 8       |       | 24   | Using where; Using temporary                        |  
| 2  | DEPENDENT SUBQUERY |       |       |               |         |         |       |      | Impossible WHERE noticed after reading const tables |  
| 3  | DERIVED            | o     | ref   | idx_2,idx_5   | idx_5   | 8       | const | 1    | Using where; Using filesort                         |  
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+ 

重写为 JOIN 之后，子查询的选择模式从 DEPENDENT SUBQUERY 变成 DERIVED，执行速度大大加快，从7秒降低到2毫秒。

UPDATE operation o   
       JOIN  (SELECT o.id,   
                            o.status   
                     FROM   operation o   
                     WHERE  o.group = 123   
                            AND o.status NOT IN ( 'done' )   
                     ORDER  BY o.parent,   
                               o.id   
                     LIMIT  1) t  
         ON o.id = t.id   
SET    status = 'applying'  

（编辑：ASP站长网）