Buy Percona ServicesBuy Now!

EXPLAIN FORMAT=JSON provides insights on optimizer_switch effectiveness

 | December 28, 2015 |  Posted In: EXPLAIN, EXPLAIN FORMAT=JSON is cool!, JSON, MySQL


EXPLAIN FORMAT=JSONThe previous post in the EXPLAIN FORMAT=JSON is Cool! series showed an example of the query select dept_name from departments where dept_no in (select dept_no from dept_manager where to_date is not null), where the subquery was materialized into a temporary table and then joined with the outer query. This is known as a semi-join optimization. But what happens if we turn off this optimization?

EXPLAIN FORMAT=JSON can help us with this investigation too.

First lets look at the original output again:

To repeat what happened here: the subquery was materialized into a  temporary table, then  joined with the departments table. Semi-join optimization is ON by default (as would be most likely without intervention).

What happens if we temporarily turn semi-join optimization OFF?

And then execute EXPLAIN one more time:

Now the picture is completely different. There is no nested_loop member, and instead there is an  attached_subqueries array containing a single member: the temporary table materialized from the subquery select dept_no from dept_manager where to_date is not null (including all the details of this materialization).

Conclusion: We can experiment with the value of optimizer_switch and use EXPLAIN FORMAT=JSON to examine how a particular optimization affects our queries.

Sveta Smirnova

Sveta joined Percona in 2015. Her main professional interests are problem solving, working with tricky issues, bugs, finding patterns that can solve typical issues quicker and teaching others how to deal with MySQL issues, bugs and gotchas effectively. Before joining Percona Sveta worked as a Support Engineer in the MySQL Bugs Analysis Support Group in MySQL AB-Sun-Oracle. She is the author of the book "MySQL Troubleshooting" and JSON UDF functions for MySQL.

Leave a Reply