Author - Sveta Smirnova

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

rows_examined_per_scan, rows_produced_per_join: EXPLAIN FORMAT=JSON answers on question “What number of filtered rows mean?”

At the end of my talk LinuxPiter conference, a user asked me a question: “What does the EXPLAIN ‘filtered’ field mean, and how do I use it?” I explained that this is the percentage of rows that were actually needed, against the equal or bigger number of resolved rows. While the user was happy with the answer, […]

Read more

attached_condition: How EXPLAIN FORMAT=JSON can spell-check your queries

When you work with complicated queries, especially ones which contain subqueries, it is easy to make a typo or misinterpret column name. While in many cases you will receive a 
column not found error, sometimes you can get strange results instead.
Like finding 4079 countries in Antarctica:

MySQL

mysql> select count(*) from City where CountryCode in (select CountryCode from Country […]

Read more