EXPLAIN FORMAT=JSON: order_by_subqueries, group_by_subqueries details on subqueries in ORDER BY and GROUP BY

EXPLAIN FORMATAnother 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:

The above code shows member ordering_operation of query_block  (which includes the  order_by_subqueries array) with information on how the subquery in ORDER BY  was optimized.

This is a simple example. In real life you can have larger subqueries in the  ORDER BY  clause. For example, take this more complicated and slightly crazy query:

Run a regular EXPLAIN on it. If we imagine this is a regular subquery, we won’t know if it can be cached or would be executed for each row sorted.

EXPLAIN FORMAT=JSON  provides a completely different picture:

We see that the subquery was optimized away: member optimized_away_subqueries exists, but there is no order_by_subqueries in the ordering_operation object. We can also see that the subquery was cached: "cacheable": true.

EXPLAIN FORMAT=JSON also provides information about subqueries in the  GROUP BY clause. It uses the  group_by_subqueries array in the  grouping_operation  member for this purpose.

Again, this output gives a clear view of query optimization: subquery in GROUP BY itself cannot be optimized, cached or converted into temporary table, but the subquery inside the subquery ( select dept_no, sum(salary) as s, count(emp_no) as c from salaries join dept_emp using (emp_no) group by dept_no) could be materialized into a temporary table and cached.

A regular EXPLAIN command does not provide such details:

Most importantly, we cannot guess from the output if the  DERIVED subquery can be cached.

Conlcusion: EXPLAIN FORMAT=JSON  provides details on how subqueries in ORDER BY and GROUP BY clauses are optimized.

Share this post