order by を手なづける
order by が信じられないぐらい遅いときがあります。
次の例を見てください。
A.
まず、ソートなしでクエリーを実行します。
select SQL_CALC_FOUND_ROWS task_name from task where task.task_starttime <= '2008/6/18' and task.task_endtime >= '2008/6/5' limit 0,1; 1 row in set (3.93 sec)
explain
+----+-------------+-------+-------+-----------------+-----------------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+-----------------+-----------------+---------+------+-------+-------------+ | 1 | SIMPLE | task | range | index_task_time | index_task_time | 9 | NULL | 50096 | Using where | +----+-------------+-------+-------+-----------------+-----------------+---------+------+-------+-------------+
B.
order by をつけます。ソートするのは、 index にも登録されている task.task_starttime です。
select SQL_CALC_FOUND_ROWS task_name from task where task.task_starttime <= '2008/6/18' and task.task_endtime >= '2008/6/5' order by task.task_starttime limit 0,1; 1 row in set (8.37 sec)
explain
+----+-------------+-------+-------+-----------------+-----------------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+-----------------+-----------------+---------+------+--------+-------------+ | 1 | SIMPLE | task | index | index_task_time | index_task_time | 18 | NULL | 100192 | Using where | +----+-------------+-------+-------+-----------------+-----------------+---------+------+--------+-------------+
| ソートあり | 1 row in set (3.93 sec) |
| ソートなし | 1 row in set (8.37 sec) |
2倍以上の違いです。
そもそも、たった10万のソートするのに4秒とは少々遅すぎます。
いったいなぜでしょうか?
C.
クエリを変更します。 select task.task_name から プライマリーキー task.task_key に変更します。
select SQL_CALC_FOUND_ROWS task.task_key from task where task.task_starttime <= '2008/6/18' and task.task_endtime >= '2008/6/5' order by task.task_starttime limit 0,1; 1 row in set (0.01 sec)
explain
+----+-------------+-------+-------+-----------------+-----------------+---------+------+-------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+-----------------+-----------------+---------+------+-------+--------------------------+ | 1 | SIMPLE | task | range | index_task_time | index_task_time | 9 | NULL | 50096 | Using where; Using index | +----+-------------+-------+-------+-----------------+-----------------+---------+------+-------+--------------------------+
爆早です。どうやらソートの有無に時間がかかったのではないようです。
これによってわかるのは、 order by が実行されるときに、 select する表を作り上げているのではないかという疑問です。
where して order by したものを表示せよと命令しているわけですから、 where して order by して残った結果を select すればいいはずです。
この場合、 where も order by も index に格納されている値を利用しているのですから、where も order by も瞬間的に終わってもいいはずです。そして、残った行の中で必要なものだけを持ってきてもらえばいい。それは、 limit 1 だけから、1行にしかならないので、 index から 実体へのシークのペナルティもそんな高くはつかないだろうと思います。しかし、現実は違います。select にあげられたものをすべて結合して表を作成し、そこから、 where して order by しているように見えます。そして、その結果が 8.37 sec もかかってしまったと。
indexに入っていないものを select しながら、 order by してはいけないのです。
