Databases Reference
In-Depth Information
The EXPLAIN Statement
You'll sometimes find that MySQL doesn't run queries as quickly as you expect. For
example, you'll often find that a nested query runs slowly. You might also find—or, at
least, suspect—that MySQL isn't doing what you hoped, because you know an index
exists but the query still seems slow. You can diagnose and solve query optimization
problems using the
EXPLAIN
statement.
The
EXPLAIN
statement helps you learn about a
SELECT
query. Specifically, it tells you
how MySQL is going to do the job in terms of the indexes, keys, and steps it'll take if
you ask it to resolve a query. Let's try a simple example that illustrates the idea:
mysql>
EXPLAIN SELECT * FROM artist;
+---+------------+-------+-----+--------------+-----+--------+-----+-----+------+
|id |select_type |table |type |possible_keys |key |key_len |ref |rows |Extra |
+---+------------+-------+-----+--------------+-----+--------+-----+-----+------+
| 1 |SIMPLE |artist |ALL |NULL |NULL | NULL |NULL | 6 | |
+---+------------+-------+-----+--------------+-----+--------+-----+-----+------+
1 row in set (0.10 sec)
The statement gives you lots of information. It tells you in this example that:
• The
id
is 1, meaning the row in the output refers to the first (and only!)
SELECT
statement in this query. In the query:
SELECT * FROM artist WHERE artist_id in (SELECT artist_id FROM played);
each
SELECT
statement will have a different
id
in the
EXPLAIN
output.
• The
select_type
is
SIMPLE
, meaning it doesn't use a
UNION
or subqueries.
• The
table
that this row is referring to is
artist
.
• The
type
of join is
ALL
, meaning all rows in the table are processed by this
SELECT
statement. This is often bad—but not in this case—and we'll explain why later.
• The
possible_keys
that could be used are listed. In this case, no index will help
find all rows in a table, so
NULL
is reported.
• The
key
that is actually used is listed, taken from the list of
possible_keys
. In this
case, since no key is available, none is used.
• The
key_len
(key length) of the key MySQL plans to use is listed. Again, no key
means a
NULL
key_len
is reported.
• The
ref
(reference) columns or constants that are used with the key is listed. Again,
none in this example.
• The
rows
that MySQL thinks it needs to process to get an answer are listed.
• Any
Extra
information about the query resolution is listed. Here, there's none.
In summary, the output tells you that all rows from the
artist
table will be processed
(there are six of them), and no indexes will be used to resolve the query. This makes
sense and is probably exactly what you expected would happen.
