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.