Buy Percona ServicesBuy Now!

EXPLAIN FORMAT=JSON: everything about attached_subqueries, optimized_away_subqueries, materialized_from_subquery

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



The regular EXPLAIN command already provides some information about subquery optimization. For example, you can find out if the subquery is dependent or not, and (since version 5.6) if it was materialized:

However, you can’t find details on exactly how this subquery was materialized. To find out more, use EXPLAIN FORMAT=JSON:

We can see here that the new temporary table ( "using_temporary_table": true) with internal name <subquery2> was materilized from the subquery ( materialized_from_subquery). We also see how this new temporary table was accessed.

We also can see that the member that contains information about the materialized subquery is part of the  nested_loop array. Nested loop optimization applies to a regular JOIN command. In this case, it means that the MySQL Server would join the temporary table with an outer query.

Another interesting case is optimized_away_subqueries: this command shows subqueries that were executed only once and were replaced by their result. A regular EXPLAIN command provides no information about such optimization:

We can see that in select_type: SUBQUERY, the output of SHOW WARNINGS  doesn’t give us much insight either.

However,  EXPLAIN FORMAT=JSON tells us how this subquery was optimized:

After seeing optimized_away_subqueries and "cacheable": true, we know that the subquery was executed only once, and the result was cached and used to resolve the outer query.

Another subquery type that I want to mention in this post is attached_subqueries – which covers subqueries that are not converted to JOIN.

For these subqueries, using this option shows if they are dependent and/or cacheable. There is also a  query_block member inside the  attached_subqueries object which provides all the details on how the query was optimized.

Conclusion: EXPLAIN FORMAT=JSON  provides some great details on subqueries optimization.

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