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.

Recommended Posts

Tune your query with EXPLAIN
Automate your work with Rake
Clean your code with Butter Knife
Create your own Solr Function Query
Talking about stumbling with SQLite query
Share it with your Instagram story!
Decorate your Sinatra app with CSS
Check your internet connection with Alamofire