Buy Percona ServicesBuy Now!

EXPLAIN FORMAT=JSON has details for subqueries in HAVING, nested selects and subqueries that update values

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


EXPLAIN FORMAT=JSONOver several previous blog posts, we’ve already discussed what information the  EXPLAIN FORMAT=JSON output provides for some subqueries. You can review those discussions here, here and here. EXPLAIN FORMAT=JSON shows many details that you can’t get with other commands. Let’s now finish this topic and discuss the output for the rest of the subquery types.

First, let’s look at the subquery in the  HAVING clause, such as in the following example:

This example prints the number of employees and their salaries, if their salary is greater than the average salary in their department. EXPLAIN FORMAT=JSON provides a lot details on how this subquery is optimized:

We see that the subquery in the  HAVING clause is not dependent, but cacheable:

It has its own query block:

Which accesses table “t”:

Table “t” was also materialized from the subquery:

Another kind of subquery is in the  SELECT list. If we want to compare the salary of an employee with the average salary in the company, for example, we can use the query select emp_no, salary, (select avg(salary) from salaries) from salaries. Lets examine the  EXPLAIN output:

EXPLAIN FORMAT=JSON in this case shows that the subquery is part of the first query_block, not dependent and cacheable.

The last type of subquery I want to discuss is the subquery updating values. For example, I added a new column to the titles table from the standard employees database:

Now I want full_title to contain both the department’s name and title, separated by a space. I can use  UPDATE with the subquery to achieve this:

To find out how it is optimized, we can use EXPLAIN FORMAT=JSON:

We can see in this output that the subquery is dependent, not cacheable, and will be executed for each row that needs to be updated.

Conclusion: EXPLAIN FORMAT=JSON  provides various information about all kind of subqueries.

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