MySqlチューニング - 複合INDEX

動画サイトのlogを調べたところ、DBアクセスがボトルネックになっているようだと判明。

Completed in 0.58115 (1 reqs/sec) | Rendering: 0.16142 (27%) | DB: 0.41544 (71%) | 200 OK 

おぉ、select文だけなのに処理全体の71%も食ってるじゃないか。
一番重いクエリは↓

SELECT *,
 (select count(*) from mylists
 where videos.id = mylists.video_id
 and create_datetime between '2008-09-10' and ADDTIME(UTC_TIMESTAMP, '09:00')
 ) as mylist_num,
 (select count(*) from played_videos
 where videos.id = played_videos.video_id
 and create_datetime between '2008-09-10' and ADDTIME(UTC_TIMESTAMP, '09:00')
 ) as play_time_num,
 (select count(*) from comments
 where videos.id = comments.video_id
 and create_datetime between '2008-09-10' and ADDTIME(UTC_TIMESTAMP, '09:00')
 ) as comment_num
 FROM `videos` WHERE (secret_flg in(0, 0)
) ORDER BY play_time_num desc LIMIT 0, 30;

副問い合わせを一つずつ削除して実行してみたところ、played_videosテーブルへの副問い合わせで処理全体の9割以上の時間を使っていることがわかった。
ここはINDEXでしょということで、複合INDEXを↓の文で作成。

CREATE INDEX playtimes ON played_videos (video_id, create_datetime);

複合INDEXについてはhttp://dev.mysql.com/doc/refman/4.1/ja/multiple-column-indexes.htmlを参照。
indexは普通に指定しただけだとwhere文の中で複数のフィールドを結合してる場合は高速化されない。
where句で指定しているカラムを使って複合INDEXを作成しないとダメ。


・結果
INDEX張る前の処理時間は　0.217966
INDEX張った後の処理時間は0.009482
おぉ、20倍以上高速化された！INDEXすげー