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