CakePHPはよく触っていたものの、MySQLについてあまり知らなかったんですが、大規模なデータベースを扱ってみようと思い立ちいろいろ試行錯誤しています。

で、ついさっき感動したのが、40万件のレコードを扱ってるテーブルに簡単なSELECT分を投げて返ってくる時間がなんと5秒もかかっていて、なんじゃこりゃ?って首をかしげてたんですが、INDEXひとつで劇的に早くなったこと。
40万件が大規模かそうでないかはこの際おいておいて、INDEXのつけ方次第でこんなにも速度に変化があるのかと涙が出そうになった。

最初の激遅いテーブルは簡単に書くとこんな具合。

CREATE TABLE IF NOT EXISTS `shops` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `status` enum('regular', 'special') NOT NULL DEFAULT 'regular',
    `created` timestamp,
    `modified` datetime,
    PRIMARY KEY (`id`),
    KEY `status` (`status`)
) ENGINE=MyISAM

発行するクエリは

SELECT * FROM `shops` WHERE `status` = 'regular' ORDER BY `id` ASC LIMIT 1

こんなごく単純にレコード一つとってくるだけなのですが、これがなんとびっくり平均5秒!
本来こんなのろまな筈がないのですが、いったい何が起こっているのかさっぱりでした。

いくらなんでもこれは遅すぎる。
何が原因かわからなかったんでクエリから条件を一つずつ削除してみようと思い、まずはとORDER BY句を削除

SELECT * FROM `shops` WHERE `status` = 'regular' LIMIT 1

するとなんと 0.00008秒!
次にWHERE 句だけを削除しても同様に早くなった

SELECT * FROM `shops` ORDER BY `id` ASC LIMIT 1

まぁ、これで早くなるのは当然ですが、しかしなぜ最初の文だとあれほど遅いのだろうか。
エンジンはMyISAMだし、status にインデックス振ってるし、id も当然 PRIMARY KEY にしているし。。。
で、試しに id と status を複数選択してインデックスを追加。

ALTER TABLE  `shops` ADD INDEX (  `id` ,  `status` ) ;

これが実は成功の一歩手前だったんですが、効果がほんの少しあったようで3.5秒程度にはなった。
が、それでも全然遅い。

で、たしかINDEXの順番とWHERE句の順番を同じにしないと意味がないってことだったので、WHERE句ではないと思いながらも
id, status
の順番を逆に
status, id
にして再度INDEXを設定した。

ALTER TABLE  `shops` ADD INDEX (  `status` ,  `id` ) ;

すると!なんと0.0004sec.!!!

この理由は、MySQLは(っていうからには他のは違うのかっていうとよく分かりませんが)1テーブルにつき1インデックスしか使用されないんだということ。
それからやはり順番は大事ってことですね。

ちなみにデータサイズは、インデックスを付加することで3.2MB増えた。
同じような複合インデックスを INT+INTでやると3.5MBの増加、INT単体の場合は2.8MBの増加だった。

あとになって「実践ハイパフォーマンスMySQL 第2版」を購入して読んだところ、とても詳しい説明があった。第3章の「スキーマの最適化とインデックス」にインデックスについて詳しく書かれてます。入門レベルの本を2冊買うのなら間違い無くこの一冊を買うのがおすすめ。(2013年11月、第3版登場「実践ハイパフォーマンスMySQL 第3版」)