Using EXPLAIN

MySQL comes with a powerful tool for investigating how the queries you issue to it are interpreted. Using EXPLAIN, you can get a snapshot of any query to find out whether you could issue it in a better or more efficient way. Following example shows how to use it with the accounts table you created earlier.

EXPLAIN SELECT * FROM accounts WHERE number='12345';

The results of this EXPLAIN command should look like the following:

+--+-----------+--------+-----+-------------+-------+-------+-----+----+------+
|id|select_type|table   |type |possible_keys|key    |key_len|ref  |rows |Extra|
+--+-----------+--------+-----+-------------+-------+-------+-----+----+-----+
| 1|SIMPLE     |accounts|const|PRIMARY      |PRIMARY|4      |const| 1  |      |
+--+-----------+--------+-----+-------------+-------+-------+-----+----+------+
1 row in set (0.00 sec)

The information that MySQL is giving you here is as follows:

select_type The selection type is SIMPLE. If you were joining tables together, this would show the join type.
table The current table being queried is accounts.
type The query type is const. From worst to best, the possible values can be: ALL, index, range, ref, eq_ref, const, system, and NULL.
possible_keys There is a possible PRIMARY key, which means that accessing should be fast.
key The key actually used is PRIMARY. This is good.
key_len The key length is 4. This is the number of bytes of the index that MySQL will use.
ref The ref column displays which columns or constants are used with the key. In this case, a constant key is being used.
rows The number of rows that need to be searched by this query is 1. This is good.

Whenever you have a query that seems to be taking longer than you think it should to execute, try using EXPLAIN to see where you can optimize it. You will discover which keys, if any, are being used, their lengths, and so on, and will be able to adjust your query or the design of your table(s) accordingly.