EmergencyEMERGENCY? Get 24/7 Help Now!

EXPLAIN FORMAT=JSON knows everything about UNIONs: union_result and query_specifications

 | January 29, 2016 |  Posted In: EXPLAIN, EXPLAIN FORMAT=JSON is cool!, JSON, MySQL


EXPLAIN FORMAT=JSONReady for another post in the EXPLAIN FORMAT=JSON is Cool series! Great! This post will discuss how to see all the information that is contained in optimized queries with UNION using the union_result and query_specifications commands.


When optimizing complicated queries with UNION, it is easy to get lost in the regular EXPLAIN  output trying to identify which part of the output belongs to each part of the UNION.

Let’s consider the following example:

While we can guess that subquery 3 belongs to the first query of the union, and subquery 6 belongs to the second (which has number 4 for some reason), we have to be very careful (especially in our case) when queries use the same tables in both parts of the UNION.

The main issue with the regular EXPLAIN for UNION  is that it has to re-present the hierarchical structure as a table. The same issue occurs when you want to store objects created in programming language, such as Java, in the database.

EXPLAIN FORMAT=JSON, on the other hand, has hierarchical structure and more clearly displays how UNION was optimized:

First it puts member union_result in the query_block  at the very top level:

The union_result object contains information about how the result set of the UNION was processed:

And also contains the  query_specifications array which also contains all the details about queries in the UNION:

This representation is much more clear, and also contains all the details which the regular EXPLAIN misses for regular queries.

Conclusion: EXPLAIN FORMAT=JSON not only contains additional optimization information for each query in the UNION, but also has a hierarchical structure that is more suitable for the hierarchical nature of the UNION.

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