[Slow query optimization] use MySQL subqueries with caution, especially when the dependent subquery tag is displayed.

Case study time:

----------------------------------------------------------------------------------------------------------------------------
1 PRIMARY Index (NULL) idx_gid 5 (NULL) Using where
2 Index_subquery id_shop_goods, idx_sid, idx_gid 5 func 1 Using where

.

.

Status = 0 group by gidTo get a large result set t1, the data volume is in rows = 850672.

...... Even if indexes are used in the two queries, it is not surprising that it is not slow.

The execution efficiency of subqueries is restricted by the number of records in the outer query, so it is better to split it into two separate query orders for execution..

, As shown below:

----------------------------------------------------------------------------------------------------------------------------------
1 PRIMARY ALL (NULL) 30 Using temporary; Using filesort
1 PRIMARY g1 ref idx_gid 5 g2.gid 1 Using where
2 Shop_goods range id_shop_goods, idx_sid id_shop_goods 5 (NULL) 30 Using where; Using index

):

Obtain the records of fk_tid (, 6) based on the gid in the sub_test table,

Then, enter tid = 2, 3, 4, 5, and 6 in test to obtain the query data.

| Film_actor | eq_ref | PRIMARY, idx_fk_film_id |

. (Note: Try SELECT goods_id, GROUP_CONCAT (cast (id as char) on our database ))

When is subquery good?

Reference resources:

@ Yi Du-Pan junyong: Xu Shiwei this ppt, covering all his understanding of golang, we recommend that you read: http://t.cn/zRI8tIH