The "MySQL" mysql/mariadb Optimizer's handling of in subquery

Reference: http://codingstandards.iteye.com/blog/1344833

In the above reference article, "High performance MySQL," fourth chapter fourth section in the third edition of my corresponding chapter sixth Chapter fifth section

Recently analyzed the production environment slow query, found a long time but inefficient query

MySQL version 5.5.18

SELECTLoc.cell_no asM_cellno ...
         fromBs_loc Loc Left JOINSt_stock_m m onLoc.cell_no=M.cell_noWHERE　　　　　　Loc.zone_no= 'B12'                 andLoc. Wms_picking_flag= 'CP'                 andM.cell_noinch (SELECTCell_no fromSt_stock_mWHEREGoods_noinch('1230480'))

Because the development of this piece of logic is not very clear, do not parse logically can directly goods_no out directly constrained the result set, simply from the in sub-query can not be used to the index to see how the MySQL optimizer to deal with

SELECT 
' Ma '. ' loc '. ' Cell_no ' as' M_cellno '
 from' ma '. ' Bs_loc ' loc 'JOIN' ma '. ' St_stock_m ' m '
WHERE
(' ma '. ' loc '. ' Zone_no '='B33')  and<In_optimizer>(' ma '. ' m '. ' Cell_no '),
<EXISTS>
　　　　　　　　(SELECT1 from' ma '. ' St_stock_m 'WHERE(' ma '. ' St_stock_m '. ' Goods_no '='1230480')  and (<CACHE>(' ma '. ' m '. ' Cell_no ')=' ma '. ' st_stock_m '. ' Cell_no '))))
 and(' ma '. ' loc '. ' Cell_no ')=' ma '. ' m '. ' Cell_no '))

Execution plan

In fact, the subquery returns no more than 3 result sets, usually we think that the internal will be based on the use of the result set to check, the efficiency will be very fast, but it is not

Thought the internal operation would be

Step 1:SELECTGroup_concat (Cell_no) fromSt_stock_mWHEREGoods_noinch('1230480') into @cell_no; Step 2SELECTLoc.cell_no asM_cellno ... fromBs_loc Loc Left JOINSt_stock_m m onLoc.cell_no=M.cell_noWHERELoc.zone_no= 'B12'                 andLoc. Wms_picking_flag= 'CP'                 andM.cell_noinch(@cell_no);

As stated in high performance MySQL:

Take this query to MARIADB test, it is really much better than the MySQL 5.5.18 processing effect.

SELECT 
' Ma '. ' loc '. ' Cell_no ' as' M_cellno '
 from' ma '. ' Bs_loc ' loc ' SemiJOIN(' ma '. ' St_stock_m ')JOIN' ma '. ' St_stock_m ' m '
　　　　WHERE
(
(' ma '. ' m '. ' Cell_no ')=' ma '. ' st_stock_m '. ' Cell_no ') and
(' ma '. ' loc '. ' Zone_no ')='B33')  and
(' ma '. ' St_stock_m '. ' Goods_no '='1230480')  and
(' ma '. ' loc '. ' Cell_no ')=' ma '. ' st_stock_m '. ' Cell_no ')
)

Execution plan

MARIADB Optimizer rewrite after the use of the semi join, this piece MARIADB official website has some description:

https://mariadb.com/kb/en/mariadb/semi-join-materialization-strategy/

MySQL Technology Insider: SQL Programming in the MARIADB optimizer for sub-query and join optimization section

Other posts for MySQL5.5 and MariaDB5.3 optimizer comparisons:

Http://blog.sina.com.cn/s/blog_aa8dc60801012pzc.html

Http://www.server110.com/mariadb/201310/2245.html

The "MySQL" mysql/mariadb Optimizer's handling of in subquery