ordering_operation: EXPLAIN FORMAT=JSON knows everything about ORDER BY processing

EXPLAIN FORMAT=JSONWe’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 temporary table and filesort were used. However, we can’t identify if these were applied to DISTINCT, or to ORDER BY, or to any other part of the query.

EXPLAIN FORMAT=JSON tells us exactly what happened:

In the output above you see can see that  ordering_operation does not use filesort:

But DISTINCT does:

If we remove the  DISTINCT clause, we will find that  ORDER BY started using filesort, but does not need to create a temporary table:

This means that in the case of the first query, a sorting operation proceeded in parallel with the duplicate keys removal.

Conclusion: EXPLAIN FORMAT=JSON  provides details about ORDER BY  optimization which cannot be seen with a regular EXPLAIN operation.

Share this post