mysql> explain select actor.first_name,actor.last_name,count(*) from sakila.film_actor inner join sakila.actor using(actor_id) group by film_actor.actor_id\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 200 Extra: Using temporary; Using filesort *************************** 2. row *************************** id: 1 select_type: SIMPLE table: film_actor type: ref possible_keys: PRIMARY,idx_fk_film_id key: PRIMARY key_len: 2 ref: sakila.actor.actor_id rows: 1 Extra: Using index 2 rows in set (0.00 sec) mysql>
从explain看，上面的写法使用了临时表和文件排序
改写后
mysql> explain select actor.first_name,actor.last_name,c.cnt from sakila.actor inner join (select actor_id,count(*) as cnt from sakila.film_actor group by actor_id) as c using(actor_id)\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: actor type: ALL possible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 200 Extra: NULL *************************** 2. row *************************** id: 1 select_type: PRIMARY table: <derived2> type: ref possible_keys: <auto_key0> key: <auto_key0> key_len: 2 ref: sakila.actor.actor_id rows: 27 Extra: NULL *************************** 3. row *************************** id: 2 select_type: DERIVED table: film_actor type: index possible_keys: PRIMARY,idx_fk_film_id key: PRIMARY key_len: 4 ref: NULL rows: 5462 Extra: Using index 3 rows in set (0.00 sec) mysql>
如果是有过滤条件的子查询，查询过滤条件尽量加到子查询条件中，而不要加到外面
原文：http://www.cnblogs.com/walter371/p/4170982.html