MySQLのサブクエリは、一見問題なさそうなものも相関サブクエリに置換されて、極端に遅くなることがありますね。
INを適切に処理できないのが原因らしいです。
問題
今回は、以下のような「ユーザごとにスコアの履歴を保持していて、最新のスコアが有効」というテーブル設計で、「全ユーザの最新スコア合計を取得」しようとして問題が起きました。
- 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に使ってしまうというわけですね。