MySQLのサブクエリは、一見問題なさそうなものも相関サブクエリに置換されて、極端に遅くなることがありますね。
INを適切に処理できないのが原因らしいです。

なぜMySQLのサブクエリは遅いのか。

今回は、以下のような「ユーザごとにスコアの履歴を保持していて、最新のスコアが有効」というテーブル設計で、「全ユーザの最新スコア合計を取得」しようとして問題が起きました。

score_log

id	int	primary key auto_increment
user_id	int	※外部キー
score	int

最初考えたSQLは以下のようなものです。
ユーザごとの最新スコアIDを一括取得し、それに該当するスコアの合計を計算しました。

SELECT SUM(score_log.score) FROM score_log WHERE score_log.id IN (SELECT MAX(log2.id) FROM score_log AS log2 GROUP BY log2.user_id)

しかしこれだと、EXPLAINをつけると分かるように、DEPENDENT SUBQUERYになってしまいます。
参考サイトによると、MySQLがINをEXISTSに変換する際に、相関サブクエリにしてしまっているようです。
実行速度も、データ件数の二乗に比例し、極端に遅くなってしまいました。

結果的に、以下のクエリに変更して解決しました。

SELECT SUM(score_log.score) FROM score_log, (SELECT MAX(log2.id) id FROM score_log AS log2 GROUP BY log2.user_id) each_cur WHERE score_log.id = each_cur.id

ユーザごとの最新スコアIDの一覧をテーブルとして、それをFROMに使ってしまうというわけですね。

Tags: MySQL