MySQL Optimization---in-type subquery, exists subquery, from-type subquery

In -type sub-query leads to traps: (Sweep less rows, not temporary tables, do not sort files quickly) title: In the Ecshop Mall table, query 6th items, (note, number 6th is a large column) the most intuitive: MySQL> SelectGoods_id,cat_id,goods_name fromGoodswherecat_idinch(Selectcat_id fromCategorywhereparent_id=6), misunderstanding: Give us the feeling is, first found in the inner layer of column 6th sub-column, such as 7,8,9, Onethen the outer layer, cat_idinch(7,8,9, Onefact: For example, goods table full scan, and row by line and category table comparison, see parent_id=6 is established

reason: MySQL's query optimizer, optimized for in type, was changed to exists execution. When the goods table is larger, the query is slower. Improved: Use a connection query instead of a subquery explainSelectGoods_id,g.cat_id,g.goods_name fromGoods asgInner Join(Selectcat_id fromCategorywhereparent_id=6) ast using (cat_id) \g inner layerSelectcat_id fromEcs_categorywhereparent_id=6Use the parent_id index to return 4 rows+--------+|cat_id|+--------+|      7 ||      8 ||      9 ||      One |+--------+ form result, set to T*************************** 3. Row***************************ID:2select_type:derivedTable: Ecs_category type:refpossible_keys:parent_idKey: parent_id Key_len:2ref:rows:4Extra:3Rowsinch Set(0.00sec) 2nd queries, T and goods are connected by cat_id because CAT_ID has an index in the goods table, so it is equivalent to 7,8,911to quickly match the rows on the goods.*************************** 2. Row***************************ID:1Select_type:PRIMARY        Table: G type:refpossible_keys:cat_idKey: cat_id Key_len:2ref:t.cat_id rows:6Extra: The 1th query: is the above 2 times the intermediate results, directly retrieved.*************************** 1. Row***************************ID:1Select_type:PRIMARY        Table:<Derived2>Type: AllPossible_keys:NULL          Key:NULLKey_len:NULLRef:NULLrows:4extra:exists Sub-query: The query has a product column. As we understand above, we use join to operate as follows: MySQL> SelectC.cat_id,cat_name fromEcs_category asCInner JoinGoods asg onc.cat_id=g.cat_idGroup  byCat_name; (see 36) Optimization 1: When the group, with the Index column group, the speed will be slightly faster, in addition, with the int type is more than the char type grouping, also faster. (see 37) Optimization 2: In group, we assume that only the contents of table A are taken,Group  byColumns, try to use the columns of Table A, which is faster than the columns of table B. (see 38) Optimization 3: Semantic optimizationSelectCat_id,cat_name fromEcs_categorywhere exists(Select * fromGoodswheregoods.cat_id=ecs_category.cat_id) (See all)|        $ | 0.00039075 | SelectC.cat_id,cat_name fromEcs_category asCInnerJoinGoods asG onc.cat_id=g.cat_idGroup  byCat_name||       Panax Notoginseng | 0.00038675 | SelectC.cat_id,cat_name fromEcs_category asCInnerJoinGoods asG onc.cat_id=g.cat_idGroup  bycat_id||        - | 0.00035650 | SelectC.cat_id,cat_name fromEcs_category asCInnerJoinGoods asG onc.cat_id=g.cat_idGroup  byc.cat_id||        + | 0.00033500 | SelectCat_id,cat_name fromEcs_categorywhere exists(Select *  fromGoodswheregoods.cat_id=ecs_category.cat_id)| fromType sub-query: NOTE: The inner-layer from statement found in the temporary table, is not indexed. Because it is a result of a temporary formation. So, the return content of the from should be as few as possible.

MySQL Optimization---in-type subquery, exists subquery, from-type subquery