Author - Sveta Smirnova

EXPLAIN FORMAT=JSON provides insights on optimizer_switch effectiveness

optimizer_switch

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

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_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