予想以上の効果がでたので記事に残します。
DBの情報を元に統計を行うSQLとプログラムの性能改善で、複合INDEXを試してみました。
MySQLは、4.2です。
対象となったSQLは複数ありますが、そのうちほとんどは４つのテーブルをJOINしています。取得条件を変えながら情報を取得し、最後に目的別に合算するっていうことをやっています。４つのテーブルのうち３つはデータ数200万オーバー、１つは100万オーバー。
最初は、こんな状態
1
2
3
4
5
6
7
8
| +----+-------------+-------+--------+----------------------------------------------+
| id | select_type | table | type | Extra |
+----+-------------+-------+--------+----------------------------------------------+
| 1 | SIMPLE | tb | range | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | tc | ref | Using where |
| 1 | SIMPLE | tp | ref | Using where |
| 1 | SIMPLE | td | eq_ref | Using where |
+----+-------------+-------+--------+----------------------------------------------+ |
まずは、一番最初に検索されている
tb に複合インデックス（検索条件カラム、取得カラム全部、合計１１カラム）を設定してみるも結果変わらず。カラムを検索条件だけにしてみたり、更に減らしたりと試すも結果変わらず。
とりあえず、最初に設定した複合インデックスを設定して、
tcにも複合インデックス（全８カラム）を設定してみる。
すると変化が現れた。
1
2
3
4
5
6
7
8
| +----+-------------+-------+--------+----------------------------------------------------------+
| id | select_type | table | type | Extra |
+----+-------------+-------+--------+----------------------------------------------------------+
| 1 | SIMPLE | tc | ref | Using where; Using index Using temporary; Using filesort |
| 1 | SIMPLE | tb | ref | Using where |
| 1 | SIMPLE | tp | ref | Using where |
| 1 | SIMPLE | td | eq_ref | Using where |
+----+-------------+-------+--------+----------------------------------------------------------+ |
に設定したINDEXが利用されてuse indexが出てきたのはわかるが、テーブルをJOINする順番が変わっている！！気になって、
tb に設定した複合INDEXを削除すると、また
tb からJOINされるようになった、possible key（利用可能index)にはいて、key（利用index）にはでてこないけど、なんらかの影響は及ぼしているようだ。
td tpにも同じ要領で検索対象、取得対象のカラムに複合INDEXを設定する。その結果、
td tpにもuse indexが表示されるようになった。
1
2
3
4
5
6
7
8
| +----+-------------+-------+------+----------------------------------------------------------+
| id | select_type | table | type | Extra |
+----+-------------+-------+------+----------------------------------------------------------+
| 1 | SIMPLE | tc | ref | Using where; Using index Using temporary; Using filesort |
| 1 | SIMPLE | tb | ref | Using where |
| 1 | SIMPLE | tp | ref | Using where; Using index |
| 1 | SIMPLE | td | ref | Using where; Using index |
+----+-------------+-------+------+----------------------------------------------------------+ |
だが、肝心のusing temporary、using filesortが消えない。using filesortって、今回のSQLにはgroup by は入っているけど、order byは入っていない。いくつかサイトを見たけど、order by には言及してあるものが多かった。そこで、order by をgroup by に読み替えて、実験してみることにした。
http://www.mysqlpracticewiki.com/index.php/Extra_field
上記のサイトと、リンク先を読んでいて今回のケースに当てはまりそうなものは、最初にJOINされるテーブルではないカラムで、group by を掛けていること。
tbのカラムでかけていた。
幸い
tc tbは１対１対応のテーブルでで、group by を
tc のカラムでかけても取得結果は変わらないので、試してみることに。
その結果がこれ
1
2
3
4
5
6
7
8
| +----+-------------+-------+------+--------------------------+
| id | select_type | table | type | Extra |
+----+-------------+-------+------+--------------------------+
| 1 | SIMPLE | tc | ref | Using where; Using index |
| 1 | SIMPLE | tb | ref | Using where |
| 1 | SIMPLE | tp | ref | Using where; Using index |
| 1 | SIMPLE | td | ref | Using where; Using index |
+----+-------------+-------+------+--------------------------+ |
消えた！やった！
ってことで、実行されるSQL（20ちょいくらい）にexplainをかけてみた。サブクエリ以外でexplainをとってみると、ほとんどのものでusing temporary、using filesortが消えていた。消えなかったのは、取得したいデータの量的にどうしようもなさそうなもの（データの半分以上を一旦取得するもの）くらい。
これで統計プログラムを実行したところ、実行時間が40分オーバーから5分前後へ！
かなり早くなった。予想以上の結果にびっくりしたので、メモ！
複合INDEXについては、以下のサイトも参考にさせていただきました。
http://d.hatena.ne.jp/k_yamamot/20100831/1283262245
http://txqz.net/blog/2006/12/13/0943
http://dev.seesaa.net/article/238633.html