where句とorder句のテーブルを集中させて最適化

Mysql 5.5.20

RailsでAR使ってたら遅いクエリーが

HABTMで
tags <-- pictures_tags --> pictures

の3枚のテーブルが主役。

desc `pictures_tags`
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| picture_id | bigint(20) unsigned | NO   | MUL | NULL    |                |
| tag_id     | bigint(20) unsigned | NO   | MUL | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+

tags, picturesはいろいろ詰まってる。

tag_id指定でpicturesにあるカラムでソートしたい

普通にやると、explainのextraで
Using temporary; Using filesort
って出る。最悪。
(explainコピペるの忘れた。。

要はjoinしてからorderかけるもんで、
テンポラリーテーブルを作るわファイルソート（メモリ上でクイックソート）はするわでkuso遅い。

原因は
where句で指定しているカラムはtagsにあるし、
order句で指定しているカラムはpicturesにあるので、
一気にインデックス使ってできねえよ、
てことらしい。

参考：
http://nippondanji.blogspot.jp/2009/03/using-filesort.html

order句、where句で指定するカラムをひとつのテーブルに

そーすっと
Using temporary; Using filesort
が無くなるはず。

要するにpicturesテーブルのカラムを、
pictures_tagsテーブルにもってきただけ。

+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| picture_id | bigint(20) unsigned | NO   | MUL | NULL    |                |
| tag_id     | bigint(20) unsigned | NO   | MUL | NULL    |                |
| sort_suru_key | bigint(20) unsigned | YES  |     | 0       |                |
+------------+---------------------+------+-----+---------+----------------+

後からソート用の複合インデックス（tag_id, sort_suru_key）は貼った。

HABTM構成だったからPKないし

RailsはHABTMでPKあると怒る。
だから作ってなかったんだけど、カラム増やすならあった方いいかなと。

alter table pictures_tags add id bigint unsigned not null AUTO_INCREMENT first, ADD INDEX(`id`), ADD PRIMARY KEY(`id`);

Mysqlのドキュメント読んでこんなクエリーになった。
初めて知ったことがあるのでメモ

• PRIMARY KEYはKEYじゃないとNGなので、後から足す時は同時にADD INDEXもする。
• after カラム名はできるけど、beforeはないので先頭追加のときはfirstってうつ
• そもそも、ALTER TABLE構文のケツにADD PRIMARY KEY(id)ってつけられたんだ。

MySQL音痴なのがバレますね。

まとめ

殴り書きになってしまい申し訳ないですが、感想としては、、、

• MySQLのパフォーマンスチューニングはexplainの語句の意味分かれば結構簡単にやれる。
• 単純にインデックス貼っとけばOKってのはデータが少ないうちだけ。Mysqlのクエリーオプティマイザの気持ちを知ろう。

ああテキトー
だけど、クエリーは爆速になりました。測定してないけど。。。