Learning MySQL

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. ...