TL;DR
ヒント句を使ってWHEREでのINDEXを使わないようにする。
また、同時にORDER BYでINDEXを使うように明示すると可読性が上がる。
事例: WHEREで十分にデータを絞れないが、ORDER BY + LIMITを使う
ORDER BYとWHEREに同じカラムを使用しており、WHEREの方で十分にデータを絞りきれないが、ORDER BY + LIMITで一部のデータを取得すればいいというケースを考える。
テーブル構造とSQL
以下のようなテーブルについて、投稿から30分以上経過しているものを、投稿日時の降順で10件取得したいとする。
| 投稿ID(id) | 投稿日時(post_datetime) | 投稿ステータス, etc |
|---|---|---|
| 9999 | 2021-04-06 12:10:00 | PUBLIC, etc |
| 9998 | 2021-04-06 11:56:10 | PUBLIC, etc |
| 9997 | 2021-04-06 11:52:06 | PUBLIC, etc |
| 9996 | 2021-04-06 12:00:00 | PUBLIC, etc |
| ... | ... | ... |
| 1 | 2020-01-01 00:00:00 | PUBLIC, etc |
PK: id
INDEX: post_datetime DESC※レコード作成時にpost_datetimeが入力されるとは限らないことや予約投稿機能等でid順とpost_datetime順が一致しない可能性あり
SQLは以下の通り。
SELECT *
FROM post
WHERE post_datetime <= NOW() - INTERVAL 30 DAY_MINUTE
ORDER BY post_datetime DESC
LIMIT 10EXPLAINするとINDEXは使われるのだが、rowsがほぼ全データになってしまい、あまり意味のないINDEXの使われ方になってしまう。（今はWHEREがpost_datetimeだけなのでExtraにはUsing index conditionと表示される分、まだ意味のあるINDEXだが、他の条件がWHEREに入ったらUsing index condition; Using whereとなるためINDEXだけで完結せずデータアクセスが走ってしまい、ほぼ意味がなくなる。）
ヒント句でWHEREでINDEXを使わないようにする
解決策として、ヒント句（インデックスヒント）でWHEREでINDEXを使わないようにする。
先ほどのSQLにヒント句を加える。
SELECT *
FROM post
IGNORE INDEX FOR JOIN (post_datetime)
WHERE post_datetime <= NOW() - INTERVAL 30 DAY_MINUTE
ORDER BY post_datetime DESC
LIMIT 10これでEXPLAINすると、keyにpost_datetimeが採用されるのにExtraはUsing whereとなる。rowsは10に絞られている。（他の条件をWHEREに追加しても同じ。）
つまりORDER BY狙いのINDEXとLIMITの効果が発揮され十~数十件程度のINDEX走査とデータアクセスしか走らないことになる。
ヒント句でORDER BYのINDEXを明示する
WHERE句のINDEX利用に関わるヒント句（FOR JOIN）をつけるだけで問題なく動くが、SQLの可読性のためにORDER BYのINDEXも併せて記載したほうがいいと思う。（ORDER BYでINDEX利用をしたいということの明示。ヒント句がついていると、SQLを見たときに「このヒント句はなんのため？」と考えてしまうため。）
SELECT *
FROM post
IGNORE INDEX FOR JOIN (post_datetime)
USE INDEX FOR ORDER BY (post_datetime)
WHERE post_datetime <= NOW() - INTERVAL 30 DAY_MINUTE
ORDER BY post_datetime DESC
LIMIT 10備考
動作確認環境
- MySQL 8