Optimizing Your Queries

Query optimization has a lot to do with the proper use of indexes. The EXPLAIN command will examine a given SELECT statement to see whether it's optimized the best that it can be, using indexes wherever possible. This is especially useful when looking at complex queries involving JOINs. The syntax for EXPLAIN is

EXPLAIN SELECT statement

The output of the EXPLAIN command is a table of information containing the following columns:

table? The name of the table.
type? The join type, of which there are several.
possible_keys? This column indicates which indexes MySQL could use to find the rows in this table. If the result is NULL, no indexes would help with this query. You should then take a look at your table structure and see whether there are any indexes that you could create that would increase the performance of this query.
key? The key actually used in this query, or NULL if no index was used.
key_len? The length of the key used, if any.
ref? Any columns used with the key to retrieve a result.
rows? The number of rows MySQL must examine to execute the query.
extra? Additional information regarding how MySQL will execute the query. There are several options, such as Using index (an index was used) and Where (a WHERE clause was used).

The following EXPLAIN command output shows a nonoptimized query:

mysql> explain select * from grocery_inventory;
+-------------------+------+---------------+-----+--------+-----+-----+------+
| table             | type | possible_keys | key | key_len| ref | rows| Extra|
+-------------------+------+---------------+-----+--------+-----+-----+------+
| grocery_inventory | ALL  | NULL          | NULL| NULL   | NULL| 6   |      |
+-------------------+------+---------------+-----+--------+-----+-----+------+
1 row in set (0.00 sec)

However, there's not much optimizing you can do with a "select all" query except add a WHERE clause with the primary key. The possible_keys column would then show PRIMARY, and the Extra column would show Where used.

When using EXPLAIN on statements involving JOIN, a quick way to gauge the optimization of the query is to look at the values in the rows column. In the previous example, you have 2 and 1. Multiply these numbers together and you have 2 as your answer. This is the number of rows that MySQL must look at to produce the results of the query. You want to get this number as low as possible, and 2 is as low as it can go!

For a great deal more information on the EXPLAIN command, please visit the MySQL manual at http://www.mysql.com/doc/E/X/EXPLAIN.html.

Part II: Basic Language Elements

Part III: Getting Involved with the Code