EXPLAIN Demystified

Baron Schwartz

Percona Inc

EXPLAIN Demystified

Outline

• What is EXPLAIN?

• How MySQL executes queries

• How the execution plan becomes EXPLAIN

• How to reverse-engineer EXPLAIN

• Hopelessly complex stuff you'll never remember

• Cool tricks

EXPLAIN Demystified

What is EXPLAIN?

• Shows MySQL's estimated query plan

• Only works for SELECT queries

mysql> explain select title from sakila.film where film_id=5\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: film

type: const

possible_keys: PRIMARY

key: PRIMARY

key_len: 2

ref: const

rows: 1

Extra:

EXPLAIN Demystified

But first...

• How does MySQL execute queries?

• SQL => Parse Tree => Execution Plan

• Executioner looks at Execution Plan

• Executioner makes calls to Storage Engines

• MySQL does NOT generate byte-code!

EXPLAIN Demystified

The Execution Plan

• SELECT... sakila.film

JOIN sakila.film_actor USING(film_id)

JOIN sakila.actor USING(actor_id)

film film_actor actor

JOIN

One way to do it

film film_actor

actorJOIN

JOIN

The MySQL Way (TM)

EXPLAIN Demystified

Where EXPLAIN comes from

EXPLAIN Demystified

Generating EXPLAIN

• MySQL actually executes the query

• But at each JOIN, instead of executing, it fills the

EXPLAIN result set

• What is a JOIN?

–

Everything is a JOIN, because MySQL always uses nested-

loops

–

Even a single-table SELECT or a UNION or a subquery

EXPLAIN Demystified

The Columns in EXPLAIN

• id: which SELECT the row belongs to

–

If only one SELECT with no subquery or UNION, then

everything is 1

–

Otherwise, generally numbered sequentially

–

Simple/complex types

• simple: there is only one SELECT in the whole query

•

3 subtypes of complex: subquery, derived, union.

– subquery: numbered according to position in SQL text

– derived (subquery in the FROM clause): executed as a temp

table

– union: rows are spooled into a temp table, then read out with a

NULL id in a row that says UNION RESULT

EXPLAIN Demystified

The Columns in EXPLAIN

• simple subquery

mysql> EXPLAIN SELECT (SELECT 1

FROM sakila.actor LIMIT 1) FROM

sakila.film;

+----+-------------+-------+...

| id | select_type | table |...

+----+-------------+-------+...

| 1 | PRIMARY | film |...

| 2 | SUBQUERY | actor |...

+----+-------------+-------+...

EXPLAIN Demystified

The Columns in EXPLAIN

• derived table

mysql> EXPLAIN SELECT film_id FROM

(SELECT film_id FROM sakila.film) AS der;

+----+-------------+------------+...

| id | select_type | table |...

+----+-------------+------------+...

| 1 | PRIMARY | <derived2> |...

| 2 | DERIVED | film |...

+----+-------------+------------+...

EXPLAIN Demystified

The Columns in EXPLAIN

• Union

mysql> EXPLAIN SELECT 1 UNION ALL

SELECT 1;

+------+--------------+------------+...

| id | select_type | table |...

+------+--------------+------------+...

| 1 | PRIMARY | NULL |...

| 2 | UNION | NULL |...

| NULL | UNION RESULT | <union1,2> |...

+------+--------------+------------+...

EXPLAIN Demystified

The Columns in EXPLAIN

• select_type shows whether it's a simple or complex

select, and which type of complex select (PRIMARY,

SUBQUERY, DERIVED, UNION, UNION RESULT)

•

Special UNION rules: first contained SELECT has

the same type as the outer context

–

e.g. the first row in a UNION contained within a subquery

in the FROM clause says “DERIVED”

• Dependences and uncacheability

–

{DEPENDENT,UNCACHEABLE} {SUBQUERY,UNION}

– Uncacheable refers to the Item_cache, not query cache

EXPLAIN Demystified

The Columns in EXPLAIN

• table: the table accessed, or its alias

• More complicated when there's a derived table

–

<derivedN>, where N is the subquery's id column

–

Always a forward reference: the child rows are later in the

output

•

Also complicated by a UNION

–

<union1,2,3...> in the UNION RESULT, where the

referenced ids are parts of the UNION

–

Always a backwards reference: the referenced ids are

earlier in the output

EXPLAIN Demystified

Are You Ready For This?

+------+----------------------+------------+...

| id | select_type | table |...

+------+----------------------+------------+...

| 1 | PRIMARY | <derived3> |...

| 3 | DERIVED | actor |...

| 2 | DEPENDENT SUBQUERY | film_actor |...

| 4 | UNION | <derived6> |...

| 6 | DERIVED | film |...

| 7 | SUBQUERY | store |...

| 5 | UNCACHEABLE SUBQUERY | rental |...

| NULL | UNION RESULT | <union1,4> |...

+------+----------------------+------------+...

EXPLAIN Demystified

Are You Ready For This?

+------+----------------------+------------+...

| id | select_type | table |...

+------+----------------------+------------+...

| 1 | PRIMARY | <derived3> |...

| 3 | DERIVED | actor |...

| 2 | DEPENDENT SUBQUERY | film_actor |...

| 4 | UNION | <derived6> |...

| 6 | DERIVED | film |...

| 7 | SUBQUERY | store |...

| 5 | UNCACHEABLE SUBQUERY | rental |...

| NULL | UNION RESULT | <union1,4> |...

+------+----------------------+------------+...

EXPLAIN Demystified

Are You Ready For This?

+------+----------------------+------------+...

| id | select_type | table |...

+------+----------------------+------------+...

| 1 | PRIMARY | <derived3> |...

| 3 | DERIVED | actor |...

| 2 | DEPENDENT SUBQUERY | film_actor |...

| 4 | UNION | <derived6> |...

| 6 | DERIVED | film |...

| 7 | SUBQUERY | store |...

| 5 | UNCACHEABLE SUBQUERY | rental |...

| NULL | UNION RESULT | <union1,4> |...

+------+----------------------+------------+...

●

Boundaries of UNION:

first id, last id (back ref)

●

Boundaries of

DERIVED: every

subsequent id (forward

ref)

●

>= to the DERIVED id

Huh?

EXPLAIN Demystified

SQL, If You Want To Study

EXPLAIN

SELECT actor_id,

(SELECT 1 FROM sakila.film_actor

WHERE film_actor.actor_id = der_1.actor_id LIMIT 1)

FROM (

SELECT actor_id

FROM sakila.actor LIMIT 5

) AS der_1

UNION ALL

SELECT film_id,

(SELECT @var1 FROM sakila.rental LIMIT 1)

FROM (

SELECT film_id,

(SELECT 1 FROM sakila.store LIMIT 1)

FROM sakila.film LIMIT 5

) AS der_2;

EXPLAIN Demystified

The Columns in EXPLAIN

• type: the “join type”

• Really, the access type: how MySQL will access the

rows to find results

• From worst to best

–

ALL, index, range, ref, eq_ref, const, system, NULL

mysql> EXPLAIN SELECT ...

id: 1

select_type: SIMPLE

table: film

type: range

EXPLAIN Demystified

The Columns in EXPLAIN

• possible_keys: which indexes looked useful to the

optimizer

–

the indexes that can help make row lookups efficient

• key: which index(es) the optimizer chose

– the index(es) the optimizer chose to minimize overall query

cost

–

not the same thing as making row lookups efficient!

–

optimizer cost metric is based on disk reads

EXPLAIN Demystified

The Columns in EXPLAIN

• key_len: the number of bytes of the index MySQL will

use

–

MySQL uses only a leftmost prefix of the index

– multibyte character sets make byte != character

mysql> EXPLAIN SELECT ...

table: film

type: range

possible_keys: PRIMARY

key: PRIMARY

key_len: 2

EXPLAIN Demystified

The Columns in EXPLAIN

• ref: which columns/constants from preceding tables

are used for lookups in the index named in the key

column

mysql> EXPLAIN

-> SELECT STRAIGHT_JOIN f.film_id

-> FROM sakila.film AS f

-> INNER JOIN sakila.film_actor AS fa

-> ON f.film_id=fa.film_id AND fa.actor_id = 1

-> INNER JOIN sakila.actor AS a USING(actor_id);

...+-------+...+--------------------+---------+------------------------+...

...| table |...| key | key_len | ref |...

...+-------+...+--------------------+---------+------------------------+...

...| a |...| PRIMARY | 2 | const |...

...| f |...| idx_fk_language_id | 1 | NULL |...

...| fa |...| PRIMARY | 4 | const,sakila.f.film_id |...

...+-------+...+--------------------+---------+------------------------+...

EXPLAIN Demystified

The Columns in EXPLAIN

• rows: estimated number of rows to read

–

for every loop in the nested-loop join plan

–

doesn't reflect LIMIT in 5.0 and earlier

• NOT the number of rows in the result set!

mysql> EXPLAIN SELECT * FROM

sakila.film WHERE film_id > 50

rows: 511

Extra: Using where

EXPLAIN Demystified

The Columns in EXPLAIN

• filtered: percentage of rows that satisfy a condition,

in 5.1 only

• in most cases will be 0 or 100

• too complicated to explain

EXPLAIN Demystified

The Columns in EXPLAIN

• The Extra column: very important!

• Some possible values

–

Using index: covering index

–

Using where: server post-filters rows from storage engine

–

Using temporary: an implicit temporary table (for sorting or

grouping rows, DISTINCT)

• No indication of whether the temp table is on disk or in memory

–

Using filesort: external sort to order results

• No indication of whether this is an on-disk filesort or in-memory

• No indication of which filesort algorithm MySQL plans to use

EXPLAIN Demystified

An Example

mysql> EXPLAIN SELECT film_id FROM

sakila.film WHERE film_id > 50

id: 1

select_type: SIMPLE

table: film

type: range

possible_keys: PRIMARY

key: PRIMARY

key_len: 2

ref: NULL

rows: 511

Extra: Using where; Using index

EXPLAIN Demystified

Demo: Visual Explain

• Maatkit includes a tool called mk-visual explain

• It can apply the rules I've shown (plus many others)

to construct a tree that might approximate the

execution plan

EXPLAIN Demystified