Tune your query with EXPLAIN

A note when investigating how to index using EXPLAIN to improve performance. I'm using Rails, so I'm writing on that assumption.

EXPLAIN procedure

First, look at the Rails log to see what queries are being issued.

Hoge Exists (0.7ms)  SELECT  1 AS one FROM `hoges` WHERE `hoge`...

Then connect to MySQL. After selecting the database, run the above query with EXPLAIN.

mysql> EXPLAIN SELECT  1 AS one FROM `hoges` WHERE `hoge`...;
+----+-------------+-------------+------+-----------------------------------------------------------------------------------------------+---------------------------+---------+-------+------+------------------------------------+
| id | select_type | table       | type | possible_key         | key                 | key_len | ref   | rows | Extra                              |
+----+-------------+-------------+------+-----------------------------------------------------------------------------------------------+---------------------------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | hoges       | ref  | index_hoges_on_fuga  | index_hoges_on_fuga | 1023    | const |    1 | Using index condition; Using where |
+----+-------------+-------------+------+-----------------------------------------------------------------------------------------------+---------------------------+---------+-------+------+------------------------------------+
1 row in set (0.00 sec)

In this way, check the execution plan of the query and select the pasting method that gives better results. If there are multiple candidates, it is sufficient to verify that the index is not attached, that it is attached (Proposal 1), and that it is attached (Proposal 2).

How to read EXPLAIN

I especially want to check around key, key_len, rows, and Extra.

key

A key selected from possible_keys (keys listed as candidates).

key_len

Key length. The shorter one is faster.

rows

Approximate number of lines estimate.

Extra

If it is Using index, it means that the query could be solved by using the index, and it is a guide if you want to speed up by the index. By the way, in the above table, it is "Using index condition", which indicates that the query can use some of the index values.