JSON

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

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

mysql> explain format=json select emp_no, concat(first_name, ‘ ‘, last_name) f2 from employees order by (select emp_no limit 1)G
*************************** 1. […]

Read more

EXPLAIN FORMAT=JSON provides insights on optimizer_switch effectiveness

optimizer_switch

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 […]

Read more

EXPLAIN FORMAT=JSON: everything about attached_subqueries, optimized_away_subqueries, materialized_from_subquery

EXPLAIN FORMAT=JSON
The regular
EXPLAIN command already provides some information about subquery optimization. For example, you can find out if the subquery is dependent or not, and (since version 5.6) if it was materialized:

MySQL

mysql> explain select dept_name from departments where dept_no in (select dept_no from dept_manager where to_date is not null)G
*************************** 1. row ***************************
[…]

Read more

used_key_parts: EXPLAIN FORMAT=JSON provides insight into which part of multiple-column key is used

In the previous post for this ongoing “EXPLAIN FORMAT=JSON is Cool!” series, we discussed covered indexes and how the 
used_columns  array can help to choose them wisely. There is one more type of multiple-column indexes: composite indexes. Composite indexes are just indexes on multiple columns. Covered indexes are a subgroup of the larger set “composite indexes.” In this post […]

Read more

used_columns: EXPLAIN FORMAT=JSON tells when you should use covered indexes

In the “MySQL Query tuning 101” video, Alexander Rubin provides an excellent example of when to use a covered index. On slide 25, he takes the query
select name from City where CountryCode = ’USA’ and District = ’Alaska’ and population > 10000 and adds the index
cov1(CountryCode, District, population, name) on table
City. With Alex’s query […]

Read more