SQL で id を使ったページネーションの処理を書く

よく知られているように SQL の OFFSET は遅い*1ため，速さを求める我々は OFFSET に頼らずにページネーションを実現する必要があります．

ところで，たいていの場合は id が主キーとなっており，これを使ってページネーションするのがいいのでは？ と考えます．

たとえば，最新の投稿から per_page 件ずつ前に出てくる掲示板を考えます．また，ページの中で最初に出てくる投稿の id を newest_id，最後に出てくる投稿の id を oldest_id とします．

このとき，次のページ (つまり，古い投稿) を見るときのクエリ*2は次のようになります．

SELECT *
FROM posts
WHERE
  id < oldest_id
ORDER BY id DESC
LIMIT per_page

前のページ (つまり，新しい投稿) を見るときのクエリは少し考える必要があります*3．

SELECT *
FROM (
  SELECT *
  FROM posts
  WHERE
    id > newest_id
  ORDER BY id ASC
  LIMIT per_page
) AS t
ORDER BY id DESC

ここで SELECT * FROM posts WHERE id > newest_id ORDER BY id DESC LIMIT per_page と書くと，最新の投稿から順に表示されてしまいます．なぜなのかは下の図で確認してください．

max(id) = 100, newest_id = 10, per_page = 5 のとき

[100][99][98][97][96] ... [15][14][13][12][11]  [10] ... [2][1]
  (ここが取得される)      (本当に欲しいところ)   (取得対象でない)
(id=100 から id=11 の順に取得される)

そのため，まず前のページの投稿を昇順で per_page 件取ってきて，それを降順に並べかえる，という処理をしています*4．

[1][2] ... [10]  [11][12][13][14][15] ... [96][97][98][99][100]
(取得対象でない)   (ここが取得される)       (取得されない)
(id=11 から id=100 の順に取得される)

ところで

この方法ではページ数を指定して取得することができないので，諦めてください．どうすればええんや……*5

*1:OFFSET で読み飛ばした分の行も走査するため．EXPLAIN すると分かります．

*2:ここでは MySQL を仮定していますが，他でもだいたいこんな感じだと思います．

*3:サブクエリに名前を付けないと怒られるので付けた．

*4:もしくは昇順で取ってきて，アプリ側で逆順にソートするなどでもいいです．

*5:idが連続という仮定をすればうまくいけそうな気はするが，不連続だとポシャるし，そもそも単語検索のときはどうするの……？