恐怖 index を張れば張るほど遅くなるクエリー
index から実体へのシークは mysqlの速度を大幅に下げることで有名です。
事実、indexから実体に対して30%のアクセスがある場合はオプティマイザがindexを利用するのをやめるぐらいです。
シークってそんなに遅いものなのか?って思いますが、とりあえずは受け入れることにします。
MySQL では利用可能な場合でもインデックスが使用されない場合があることに注意してください。この一例として、インデックスの使用によって、MySQL がテーブルの 30% を超えるレコードにアクセスする必要が生じる場合が挙げられます（この場合は、必要なシークが大幅に減少するため、テーブルスキャンのほうが高速になる可能性が高くなります）。 ただしこのクエリに、レコードの一部のみを取り出す LIMITが使用されている場合、結果で返される少数のレコードを迅速に検索できるため、MySQL はインデックスを使用します。
http://dev.mysql.com/doc/refman/5.1/ja/mysql-indexes.html
と、書いてありますが、いろいろ試したんですが、limit があっても、where order by 等があればパフォーマンスは下がるみたいです。
A. indexなしの状態で検索してみます。
select SQL_CALC_FOUND_ROWS * from task where task.task_starttime <= '2008/6/18' and task.task_endtime >= '2008/6/5' limit 0,1; 1 row in set (0.42 sec)
explain
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE | task | ALL | NULL | NULL | NULL | NULL | 99983 | Using where | +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
0.42 sec ということで平凡な結果です。
indexをまったく利用していない状態(key = NULL) でデータベース全件スキャンが実行されています。
INDEXを張って更なる高速化を目指します。(*ﾟ∀ﾟ)=3ﾑﾊｰ
B.
indexを張ります。
create index index_task_time ON `task`(task_starttime,task_endtime);
C.
indexを張ったので早速使ってみましょう。
select SQL_CALC_FOUND_ROWS * from task where task.task_starttime <= '2008/6/18' and task.task_endtime >= '2008/6/5' limit 0,1; 1 row in set (4.08 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 | +----+-------------+-------+-------+-----------------+-----------------+---------+------+-------+-------------+
4.08 sec って、ずぎゃっああ 速度が劣化した!!
key=index_task_time なんでばっちりindexが使われているのに。
| indexなし | 0.42 sec |
| indexあり | 4.08 sec |
では、こういう状態では index はいらない子なんでしょうか?
index の恩恵を受けることはできないんでしょうか?
方法はあります。
D.
このクエリーだけを実行してみます。
select SQL_CALC_FOUND_ROWS task_key from task where task.task_starttime <= '2008/6/18' and task.task_endtime >= '2008/6/5' limit 0,1; 1 row in set (0.04 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 | +----+-------------+-------+-------+-----------------+-----------------+---------+------+-------+--------------------------+
0.04 secで劇早です。
select * から select task.task_key に変更しました。
task.task_key は、 task テーブルのプライマリキーです。
このクエリーにはindexに一致する検索しか行われていないため、 explain の Extra で Using index の称号をゲットできました。
ただこれでは、プライマリーキーしか select していないのであんまり実際には利用価値はありません。
そこで別の方法を考えます。
