These queries are not identical in cost even though they are in EXPLAIN output. With the FORMAT=JSON output also showing cost, we can see that two ranges costs 10.04, versus one big range costing 9.04 (not shown). The JSON output will now include cost information (as well as showing separate ranges as attached_condition): EXPLAIN FORMAT=JSONĮXPLAIN FORMAT=JSON was introduced in MySQL 5.6 along with OPTIMIZER TRACE, but where it really becomes useful is MySQL 5.7. It should be possible to distinguish between the two. Two distinct separate ranges may be two separate pages, and thus have different cache efficiency on the buffer pool. EXPLAIN will not show this difference, and both queries appear the same: It's pretty obvious that the second one needs to execute in two separate ranges. SELECT * FROM film WHERE (film_id BETWEEN 1 and 10) or (film_id BETWEEN 911 and 920).SELECT * FROM film WHERE film_id BETWEEN 1 and 20.Perhaps a better example to demonstrate this, is the difference between these two ranges: It is stable to compare costs between different execution plans. A single range access plus next five times costs 2.0168 instead of 6.0168:įor context, a cost unit is a logical representation of approximately one random IO. So I would say that BETWEEN 1 AND 5 is the cheapest query, because it finds one key and then says next, next, next until finished. This can also be confirmed with the handler counts from SHOW STATUS:įilm_id =1 or film_id=2 or film_id=3 or film_id=4 or film_id=5: The optimizer trace output shows that the first query executes as one range, while the second and third execute as 5 separate single-value ranges: SELECT * FROM film WHERE film_id =1 or film_id=2 or film_id=3 or film_id=4 or film_id=5.SELECT * FROM film WHERE film_id IN (1,2,3,4,5).SELECT * FROM film WHERE film_id BETWEEN 1 AND 5.Here are the outputs for the three versions of the query: You enable the optimizer trace, then you run the actual query.It doesn’t just show the intended execution plan, it shows the alternative choices.It is similar to EXPLAIN, with a few notable differences: Optimizer trace is a new diagnostic tool introduced in MySQL 5.6 to show how the optimizer is working internally. There are now a couple of useful features to show the difference ? Optimizer Trace Mysql> EXPLAIN SELECT * FROM film WHERE film_id =1 or film_id=2 or film_id=3 or film_id=4 or film_id=5\G Mysql> EXPLAIN SELECT * FROM film WHERE film_id IN (1,2,3,4,5)\G Mysql> EXPLAIN SELECT * FROM film WHERE film_id BETWEEN 1 AND 5\G Here is an example using the sakila schema: All of the queries resolve to the same output in EXPLAIN. It is an interesting question because there was no good way to answer it when it was asked in 2009. WHERE someFIELD = 1 OR someFIELD = 2 OR someFIELD = 3. I am wondering if there is any difference in regards to performance between the following: I accidentally stumbled upon this Stack Overflow question this morning: Posted on JCategories Community, Query Optimizer 3 Comments on The MySQL 5.7 Optimizer Challenge Optimizer Trace and EXPLAIN FORMAT=JSON in 5.7 The supplies of this special edition t-shirt are limited, but I will ship it to you anywhere in the world ? The MySQL 5.7 Community Contributor Polo, as modeled by Daniël van Eeden. If you can demonstrate a reproducible testcase, I have a polo with MySQL 5.7 Community Contributor on it waiting for you. The optimizer team has also allowed cost constants to be configurable on both a server and a storage engine basis, and we are confident that the default InnoDB engine will always work “as good as MyISAM” (which has a natural advantage, in that the optimizer was originally largely built around it.)įind an example where the optimizer picks the wrong execution plan for InnoDB tables but is correct for MyISAM. The hacks of storage engines lying to the optimizer are being rolled back, and your chances of getting an optimal query plan should now be much higher than in prior releases of MySQL. In the MySQL team, we have been working really hard on refactoring the optimizer and improving the cost model. jason on MySQL 5.6.24 Community Release Notes.jason on Moving to Product Management
0 Comments
Leave a Reply. |