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 non-optimized 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!

Take a look at these two queries, both are JOINs. The first one is not optimized:

mysql> explain SELECT c.id AS cat_id, c.cat_title, si.item_type, si.item_title,
    -> si.item_price, si.item_desc, si.item_image_short FROM store_categories AS c
    -> LEFT JOIN store_items AS si ON si.cat_id = c.id WHERE si.id = 1;
+-------+------+---------------+------+---------+------+------+-------------+
| table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+-------+------+---------------+------+---------+------+------+-------------+
| c     | ALL  | NULL          | NULL |    NULL | NULL |    3 |             |
| si    | ALL  | NULL          | NULL |    NULL | NULL |    7 | Using where |
+-------+------+---------------+------+---------+------+------+-------------+
2 rows in set (0.00 sec)

This query has to look at 21 rows to find its result. If we change the way the tables are JOINed, we can optimize the query to only look at 2 rows:

mysql> explain SELECT c.id AS cat_id, c.cat_title, si.item_type, si.item_title,
    -> si.item_price, si.item_desc, si.item_image_short FROM store_items AS si
    -> LEFT JOIN store_categories AS c ON c.id = si.cat_id WHERE si.id = 4;
+-------+-------+---------------+---------+---------+-------+------+-------+
| table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+-------+-------+---------------+---------+---------+-------+------+-------+
| si    | const | PRIMARY       | PRIMARY |       4 | const |    1 |       |
| c     | const | PRIMARY       | PRIMARY |       4 | const |    1 |       |
+-------+-------+---------------+---------+---------+-------+------+-------+
2 rows in set (0.03 sec

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