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> 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 ***************************
           id: 1
  select_type: SIMPLE
        table: departments
   partitions: NULL
         type: index
possible_keys: PRIMARY
          key: dept_name
      key_len: 42
          ref: NULL
         rows: 9
     filtered: 100.00
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: …

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 we’ll discuss how “used_key_parts” can help show …

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 tuning experience, making the right index decision is simple – but what about us mere mortals? If a query is more complicated, or simply uses more than one table, how do we know what to do? Maintaining another index …

At the end of my talk “Troubleshooting MySQL Performance” at the 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, I’d like to better illustrate this. And I can do it with help of EXPLAIN FORMAT=JSON and its

rows_examined_per_scan, rows_produced_per_join

  statistics.

Let’s take a simple query that searches information about the …

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> select count(*) from City where CountryCode in (select CountryCode from Country where Continent = 'Antarctica');
+----------+
| count(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.05 sec)

Or not finding any cities in Georgia:

mysql> select Name, Language from City join CountryLanguage using (CountryCode) where CountryCode in (select Code from Country where District='Georgia' and Continent='Asia');
Empty set (0.18 sec)

I used a standard …