limit/offsetについて考える

LIMIT 20 OFFSET (:page - 1) * 20 みたいなクエリは :page に大きい値が入れれるように設計されてるとクエリに殺されるので、 WHERE key = :offset_for_next_page LIMIT 20 なクエリになるよう設計してほしい。
http://twitter.com/kamipo/status/56304601049210880

俺もボスに教わるまで知らなかったのだが、

mysql> select id from mentions order by id asc limit 100, 10;

がすることは、

• データを10個だけfetchする

ではなく、

• 110個データをfetchして、先頭から100個捨てる

だ。何を今更って感じですよねー知ったのは10ヶ月ほど前でした。俺の未熟さを思い知れ。

で。このようにlimitを付けてデータを取得するときというのは、大抵データが多すぎるから小分けにして取得したいとき。
webアプリだと、その小分けにして取得した結果を1ページ目、2ページ目…として表示する。検索エンジンの検索結果ページのように。
んで当然そういう場合って、見ているページから 次のページ or 前のページ に移動したい。どうするのというと、えーと例えばデータを降順で10件ずつ取得するとすると、

• 検索条件を作成する。例えば where status_id > 100000000000 のように
• 検索条件に合うデータのidã‚’ limit 1 を使って1件だけ取得する（record_aとでもする）
  • record_a のidは 1871 とでもしておこう

mysql> select id from mentions where status_id > 100000000000 limit 1;

• 検索条件に、「record_aのidよりidが大きい」を加え、limit句の値は10にする

mysql> select * from mentions where status_id > 100000000000 and id >= 1871 limit 10;

ここまでで欲しいデータが10件取得出来た。で、「次へ or 前へ」をするには、そもそもこの次のデータ、前のデータが存在するか分かればいい。ので、

• この検索条件に該当するデータが、取得したデータの前後にそれぞれ存在か調べる。存在の有無が分かればいいので limit 1 で
  • さっき取得したデータのidの範囲は 1871 から 1880 だった、とする

mysql> select id from mentions where status_id > 100000000000 and id < 1871 limit 1;
mysql> select id from mentions where status_id > 100000000000 and id > 1880 limit 1;

• 前者の結果が存在すれば「前へ」が表示できて、後者の結果が存在すれば「次へ」が表示できる
  • 「前へ」「次へ」のページで取得するデータについては、それぞれの存在を確認したクエリで select * と limit 10 とすればいい

とか。まぁ極力無駄は抑えたんじゃなかろうか。
尚実際にこういうことをしようとするともうちょっと工夫が要る。「次へ」を見ているときに、更にそのページにおける「次へ」にあたるデータの存在の確認と、「前へ」を見ているときに更にそのページにおける「前へ」にあたるデータの存在の確認の方法は違ってくる。簡単だから頑張れ。

あとこういうことをして「あー無駄なfetchを抑えて前後へのページングも付けたぜ」とか思ってると、

googleみたいに 1 2 3... ってして各ページへのリンクも付けて

とか言われると通用しない。しませんでした。俺の頭じゃ

1. 検索条件に合うデータをcountして、その結果から計算する
2. 検索条件に合うデータのidをfetchして、その結果から計算する

しか思い付かなかった上に面倒だったので冒頭で挙げたやつに戻った。
ちゃんと考えたら案外あっさり出てきそうな気もするけど考える気がない。

尚こういう考え方もある。状況に応じて好きにしろい。

http://twitter.com/kamipo/status/56390988075237376
http://twitter.com/kamipo/status/56391325649608704

俺の場合は正確性が求められる上に、利用される個所が負荷にならない程度に少なかったので冒頭のでもよかった。
飽きて疲れて眠いので終わる。