I recently worked on an uncommon slow query: less than 100 rows were read and returned, the whole dataset was fitting in memory but the query took several seconds to run. Long story short: the query was a join involving 21 tables, running on MySQL 5.1. But by default MySQL 5.1 is not good at handling joins with such a large number of tables. The good news is that MySQL 5.6 brings welcome improvements.
Isolating the problem
As always with a slow query, finding the execution plan with EXPLAIN is the 1st step to understand where time is spent. Here the plan was very good with almost all joins using the primary key or a unique key, but perhaps the most interesting part was that EXPLAIN was very slow as well. This indicates that the optimizer takes a lot of time finding the optimal execution plan. To double check, we can run SHOW PROFILE:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | mysql> set @@profiling = 1; mysql> SELECT ....; mysql> SHOW PROFILE; +--------------------+----------+ | Status | Duration | +--------------------+----------+ | starting | 0.000783 | | Opening tables | 0.000192 | | System lock | 0.000005 | | Table lock | 0.000010 | | init | 0.000369 | | optimizing | 0.000091 | | statistics | 3.459529 | | preparing | 0.000213 | | executing | 0.000005 | | Sending data | 0.004597 | | end | 0.000008 | | query end | 0.000005 | | freeing items | 0.000066 | | logging slow query | 0.000004 | | cleaning up | 0.000006 | +--------------------+----------+ |
‘Statistics’ means ‘finding a good execution plan’. Okay, we have found why the query is slow.
If you have read this post, you already know how to fix the problem: set optimizer_search_depth = 0 and response time instantly drops to 0.02s. Also mentioned in this post, we should expect MySQL 5.6 to be much better at handling such queries. Well, let’s give it a try!
MySQL 5.6
With the default value of optimizer_search_depth, response time is 0.02s in 5.6 and we can see in SHOW PROFILE that the time spent finding a good execution plan is very small:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.002022 | | checking permissions | 0.000018 | [...] | checking permissions | 0.000015 | | Opening tables | 0.000130 | | init | 0.001093 | | System lock | 0.000048 | | optimizing | 0.000200 | | statistics | 0.009404 | | preparing | 0.000432 | | executing | 0.000029 | | Sending data | 0.005409 | | end | 0.000027 | | query end | 0.000009 | | closing tables | 0.000034 | | freeing items | 0.000082 | | cleaning up | 0.000030 | +----------------------+----------+ |
However ‘Statistics’ is still the most expensive step. What if we set optimizer_search_depth = 0? It is even better:
1 2 3 | [...] | statistics | 0.001896 | [...] |
And the response time drops to 0.01s.
Conclusions
EXPLAINis a great tool to understand how the server will run a query, but it does not tell you everything. Additional tools (here, SHOW PROFILES) are often useful as well.- By default MySQL 5.6 is much better than the previous versions at handling joins with many tables: great job optimizer team at Oracle!
- However even with 5.6, you may get better response time with
optimizer_search_depth = 0
Hi Stéphane,
Thanks for the tip !
Does changing the optimizer_search_depth=0 result in a sub-optimal execution plan? It seems like we’re telling the optimizer “don’t think about this too hard.”
@Bill Karwin
Yes, you are telling the optimizer not to think about it too hard. Sometimes the query is already optimal, or near optimal.
In those cases, trying to figure out a better plan may take more time than simply running.
In the first case analyzed, the optimization took more time than it gave.
On may queries that are NOT optimal, thinking for a greater depth may take a lot of time, but may return an optimization that is much faster, so optimization will give more than it takes.
This tip is NOT saying “Always turn optimization off” it is saying that when optimization takes more time than running without optimization, turn it off.
Hi,
Note that setting optimizer_search_depth to 0 does not mean that you turn off the join optimizer. As written in the manual:
“If set to 0, the system automatically picks a reasonable value.” It so happens that the reasonable value is 7. Hence, if set to 0 the join optimizer will try find the sequence of the first 7 tables that gives the lowest cost. However, the order of the rest of the tables will not necessarily be optimal.
As Stephane writes MySQL 5.6 is much better than earlier versions at handling joins with many tables. Jørgen has described this in his blog (http://jorgenloland.blogspot.no/2012/04/improvements-for-many-table-joins-in.html). One improvement is that MySQL now starts out with an order that is more promising than earlier. Hence, it will be more likely that costly plans are not considered since they will be pruned at an early stage (because a much cheaper plan has already been found). Also, tables where primary key look-ups are possible will be chosen before considering other tables and will not add to the “combinatorial explosion” of considered plans.
Can you please provide me the example join query with order by and group by based on percona. So that i can create my query as optimized in percona way
Karthikeyan,
My tip is not related to a specific query, it may apply when you queries spending a lot of time in the ‘Statistics’ state.