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.
