MySQL GROUP by optimization

Mysql>ExplainSelectActor.first_name,actor.last_name,Count(*) fromSakila.film_actorInner JoinSakila.actor using (actor_id)Group  byfilm_actor.actor_id\g*************************** 1. Row***************************ID:1Select_type:simpleTable: Actor Type: AllPossible_keys:PRIMARY          Key:NULLKey_len:NULLRef:NULLrows: $extra:usingTemporary; Using Filesort*************************** 2. Row***************************ID:1Select_type:simpleTable: Film_actor Type:refpossible_keys:PRIMARY, idx_fk_film_idKey:PRIMARYKey_len:2ref:sakila.actor.actor_id rows:1extra:usingIndex2Rowsinch Set(0.00sec) MySQL> 

From explain, the above notation uses temporary tables and file sorting

After rewriting

Mysql>ExplainSelectActor.first_name,actor.last_name,c.cnt fromSakila.actorInner Join(SelectACTOR_ID,Count(*) asCnt fromSakila.film_actorGroup  byACTOR_ID) asc using (actor_id) \g*************************** 1. Row***************************ID:1Select_type:PRIMARY        Table: Actor Type: AllPossible_keys:PRIMARY          Key:NULLKey_len:NULLRef:NULLrows: $Extra:NULL*************************** 2. Row***************************ID:1Select_type:PRIMARY        Table:<Derived2>Type:refpossible_keys:<Auto_key0>          Key:<Auto_key0>Key_len:2ref:sakila.actor.actor_id rows: -Extra:NULL*************************** 3. Row***************************ID:2select_type:derivedTable: Film_actor Type:IndexPossible_keys:PRIMARY, idx_fk_film_idKey:PRIMARYKey_len:4Ref:NULLrows:5462extra:usingIndex3Rowsinch Set(0.00sec) MySQL> 

If it is a subquery with filter conditions, the query filter should be added to the subquery condition as much as possible, not to the outside

MySQL GROUP by optimization