This blog is an EXPLAIN FORMAT=JSON wrap-up for the series of posts I’ve done in the last few months.

In this series, we’ve discussed everything unique to

EXPLAIN FORMAT=JSON

. I intentionally skipped a description of members such as

table_name

,

access_type

  or

select_id

, which are not unique.

In this series, I only mentioned in passing members that replace information from the

Extra

 column in the regular

EXPLAIN

 output, such as

using_join_buffer

 ,

partitions

,

using_temporary_table

  or simply

Once again it’s time for another EXPLAIN FORMAT=JSON is cool! post. This post will discuss how EXPLAIN FORMAT=JSON allows the nested_loop command to make the JOIN operation hierarchy transparent.

The regular

EXPLAIN

  command lists each table that participates in a 

JOIN

  operation on a single row. This works perfectly for simple queries:

mysql> explain select * from employees join titles join salariesG
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employees
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: …

Time for another entry in the EXPLAIN FORMAT=JSON is cool! series of blog posts. This time we’ll discuss how using EXPLAIN FORMAT=JSON allows you to see that

cost_info

  knows why the optimizer prefers one index to another.

Tables often have more than one index. Any of these indexes can be used to resolve query. The optimizer has to make a choice in this case. One of the metrics that can be used to help make the choice is the potential cost of the query evaluation.

For example, let’s take the table

titles

  from the standard employees database:

Time for another entry in the EXPLAIN FORMAT=JSON is cool! series. Today we’re going to look at how you can view the buffer result using JSON (instead of the regular

EXPLAIN

 command.

Regular

EXPLAIN

 does not identify if

SQL_BUFFER_RESULT

 was used at all. To demonstrate, let’s run this query:

mysql> explain select * from salariesG
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: salaries
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2557022
     filtered: 100.00
        Extra: …

Ready 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:

mysql> explain
    ->     select emp_no, last_name, 'low_salary' from employees
    ->     where emp_no in (select emp_no from salaries
    ->         where salary < (select avg(salary) …

Over 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.

…

We’ve already discussed using the ORDER BY clause with subqueries. You can also, however, use the 

ORDER BY

 clause with sorting results of one of the columns. Actually, this is most common way to use this clause.

Sometimes such queries require using temporary tables or filesort, and a regular

EXPLAIN

  clause provides this information. But it doesn’t show if this job is needed for

ORDER BY

 or for optimizing another part of the query.

For example, if we take a pretty simple query ( 

select distinct last_name from employees order by last_name asc

) and run

EXPLAIN

  on it, we can see that both the …

In the previous EXPLAIN FORMAT=JSON is Cool! series blog post, we discussed the  

group_by_subqueries

  member (which is child of

grouping_operation

). Let’s now focus on the 

grouping_operation

  and other details of 

GROUP BY

  processing.

grouping_operation

 simply shows the details of what happens when the 

GROUP BY

 clause is run:

mysql> explain format=json …

Another post in the EXPLAIN FORMAT=JSON is Cool! series! In this post, we’ll discuss how the EXPLAIN FORMAT=JSON provides optimization details for 

ORDER BY

 and  

GROUP BY

 operations in conjunction with 

order_by_subqueries

 and  

group_by_subqueries

. 

EXPLAIN FORMAT=JSON

 can print details on how a subquery in

ORDER BY

 is optimized:

mysql> explain format=json select emp_no, concat(first_name, ' ', last_name) f2 from employees order by (select emp_no limit 1)G
*************************** 1. row ***************************
EXPLAIN: { …

The 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. …