MySQLのオプティマイザが少し変わったらしい

MySQLを5.1.16から5.1.22にアップデートしてみたら、どうやらオプティマイザのアルゴリズムが変わったらしくページの表示が激遅になってしまいました。もともとMySQLのアップデートはPHP5にするついでだったのですが、そのままではちょっと困るのでまずはこちらの問題から片付けることにしました。

例によってスロークエリのログをとってみると、下記のようなSQLが引っかかってきました。

SELECT query_phrase, item_id, ititle, host, engine, timestamp
FROM nucleus_plugin_searched_phrase_history h
LEFT JOIN nucleus_item i
ON h.item_id=i.inumber
ORDER BY timestamp DESC
LIMIT 0, 10;

どうやら、このクエリ１つの実行に３秒近く費やしているようです。

とりあえず実行計画を確認するためにEXPLAINをしてみます。

mysql> EXPLAIN SELECT query_phrase, item_id, ititle, host, engine, timestamp FROM nucleus_plugin_searched_phrase_history h LEFT JOIN nucleus_item i ON h.item_id=i.inumber ORDER BY timestamp DESC LIMIT 0, 10;
+----+-------------+-------+--------+-----------------+---------+---------+------------------------+--------+----------------+
| id | select_type | table | type   | possible_keys   | key     | key_len | ref                    | rows   | Extra          |
+----+-------------+-------+--------+-----------------+---------+---------+------------------------+--------+----------------+
|  1 | SIMPLE      | h     | ALL    | NULL            | NULL    | NULL    | NULL                   | 180122 | Using filesort |
|  1 | SIMPLE      | i     | eq_ref | PRIMARY,inumber | inumber | 4       | cles_nucleus.h.item_id |      1 |                |
+----+-------------+-------+--------+-----------------+---------+---------+------------------------+--------+----------------+
2 rows in set (0.00 sec)

たしかにこれは酷い。。。。。。
nucleus_plugin_searched_phrase_historyの18万件を全て取り出して、しかもファイルを使ってソートしたりしているようです。このテーブルにはORDER BY句の指定に合わせて、timestampの降順でINDEXが張ってあるのにそれが使われなくなっています。

† へんなかわし方ですが・・・・

timestampに張ってあるINDEXを使わせようとFORCE INDEXを指定してみたのですが、それでも無視されてしまいました。どうやらMySQLはORDER BY句に適用できるINDEXがあったとしても、WHERE句の評価で使われなかったINDEXについては無視するようになってしまったようです。仕方がないので、timestampを使うような実質的には意味がないWHERE句^*1を追加してこの場をしのぐことにします。

mysql> EXPLAIN SELECT query_phrase, item_id, ititle, host, engine, timestamp FROM　nucleus_plugin_searched_phrase_history h LEFT JOIN nucleus_item i ON h.item_id=i.inumber WHERE timestamp IS NOT NULL ORDER BY timestamp DESC LIMIT 0, 10;
+----+-------------+-------+--------+-----------------+---------+---------+------------------------+--------+-------------+
| id | select_type | table | type   | possible_keys   | key     | key_len | ref                    | rows   | Extra       |
+----+-------------+-------+--------+-----------------+---------+---------+------------------------+--------+-------------+
|  1 | SIMPLE      | h     | range  | ts_desc         | ts_desc | 9       | NULL                   | 180122 | Using where |
|  1 | SIMPLE      | i     | eq_ref | PRIMARY,inumber | inumber | 4       | cles_nucleus.h.item_id |      1 |             |
+----+-------------+-------+--------+-----------------+---------+---------+------------------------+--------+-------------+
2 rows in set (0.00 sec)

以前のバージョンではできていたので、オプティマイザのバグなのかなぁ。。。。。
とりあえずMySQLをバージョンアップした場合にはアップデート後にslow_logを注視したほうがいいかもしれません。