When I heard that MySQL 5.6 was implementing EXPLAIN for writes (INSERT,UPDATE,DELETE,REPLACE), I was pretty excited. Then I heard that MySQL 5.6 also was implementing a JSON format for EXPLAIN and my thought was “I do not care about that!”
Boy, was I wrong. The JSON format does not just put the output into JSON format, it also gives extra information that’s actually pretty useful! It can tell you when you are doing an implicit cast, which parts of a composite index are being used, and when index condition pushdown are being used. None of these are shown in regular EXPLAIN (which seems odd, why could they extend the JSON format but not put the information into the regular EXPLAIN format?), so using the JSON format is actually a good idea even if you do not care about what format your output is in.
As a note, MySQL Workbench’s Visual Explain (go to Query->Visual Explain Current Statement) also gives this information.
attached_condition and implicit casts
In a talk about EXPLAIN I do, I use the Sakila sample database. Here is an example of a “bad” query:
mysql> EXPLAIN SELECT rental_id FROM rental WHERE
DATE(rental_date) = '2006-02-14'\G
*************************** 1. row
***************************
id: 1
select_type: SIMPLE
table: rental
type: index
possible_keys: NULL
key: rental_date
key_len: 10
ref: NULL
rows: 16005
Extra: Using where; Using index
1 row in set (0.00 sec)
This query is “bad” because it is doing a full index scan
(type: index) instead of doing a range scan for just
the range of dates we want (should be type: range).
Ironically, the EXPLAIN does not actually explain why.
However, the JSON format does explain why:
mysql> EXPLAIN FORMAT=JSON SELECT rental_id FROM rental
WHERE DATE(rental_date) = '2006-02-14'\G
*************************** 1. row
***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"table": {
"table_name": "rental",
"access_type": "index",
"key": "rental_date",
"used_key_parts": [
"rental_date",
"inventory_id",
"customer_id"
],
"key_length": "10",
"rows": 16005,
"filtered": 100,
"using_index": true,
"attached_condition": "(cast(`sakila`.`rental`.`rental_date` as
date) = '2006-02-14')"
}
}
}
Note that the attached_condition shows the implicit
cast. This is MUCH more friendly to a developer or administrator
who is trying to figure out why MySQL is not doing what they want
it to do!
The visual EXPLAIN from MySQL Workbench also shows the implicit cast:
You may also notice it shows the “filtered” attribute, which is not in regular EXPLAIN but is part of EXPLAIN EXTENDED – “filtered” is the percentage of “rows” that are estimated to be returned. A higher number here is better, if it is low it means that you are examining a lot of rows that you do not return.
used_key_parts
You may have noticed above that there is a
used_key_parts array that does not show up in the
traditional EXPLAIN. In a traditional EXPLAIN (or EXPLAIN
EXTENDED), you do get to see the index length with the
key_len field, so you can guess that only part of a
composite index is used. Both the previous query and the
following query use this index:
UNIQUE KEY `rental_date`
(`rental_date`,`inventory_id`,`customer_id`)
Here is the traditional EXPLAIN – note that it shows the
rental_date index is used, and the
key_len is 5, which infers that only the first field
fo the index, rental_date is being used, not the
other 2 id fields. But you have to deduce that for yourself:
mysql> EXPLAIN EXTENDED SELECT rental_id FROM rental
WHERE rental_date BETWEEN '2006-02-14 00:00:00' and '2006-02-14
23:59:59'\G
*************************** 1. row
***************************
id: 1
select_type: SIMPLE
table: rental
type: range
possible_keys: rental_date
key: rental_date
key_len: 5
ref: NULL
rows: 181
filtered: 100.00
Extra: Using where; Using index
Here is the JSON format, which shows the
used_key_parts field, which reveals very clearly
that only the first field of the index is used:
mysql> EXPLAIN FORMAT=JSON SELECT rental_id FROM rental
WHERE rental_date BETWEEN '2006-02-14 00:00:00' and '2006-02-14
23:59:59'\G
*************************** 1. row
***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"table": {
"table_name": "rental",
"access_type": "range",
"possible_keys": [
"rental_date"
],
"key": "rental_date",
"used_key_parts": [
"rental_date"
],
"key_length": "5",
"rows": 181,
"filtered": 100,
"using_index": true,
"attached_condition": "(`sakila`.`rental`.`rental_date` between
'2006-02-14 00:00:00' and '2006-02-14 23:59:59')"
}
}
}
And here is the MySQL Workbench Visual EXPLAIN that shows the used_key_parts clearly:
Index condition pushdown is itself a new feature in MySQL 5.6, and I will talk about it in another blog post.
I am glad I took a second look at EXPLAIN FORMAT=JSON – the new features are awesome! My only complaint is that I think they should be added to either EXPLAIN or EXPLAIN EXTENDED. I also hope that tools like pt-query-digest will be updated to use the extra information.