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

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
GROUPBY operations in conjunction with
order_by_subqueriesand
group_by_subqueries.

EXPLAINFORMAT=JSON can print details on how a subquery in
ORDER BY is optimized:

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.

EXPLAINFORMAT=JSON also provides information about subqueries in the
GROUPBY 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
GROUPBY itself cannot be optimized, cached or converted into temporary table, but the subquery inside the subquery (
selectdept_no,sum(salary)ass,count(emp_no)ascfromsalariesjoindept_empusing(emp_no)groupbydept_no) could be materialized into a temporary table and cached.

Related

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.