私が歌川です

@utgwkk が書いている

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が連続という仮定をすればうまくいけそうな気はするが,不連続だとポシャるし,そもそも単語検索のときはどうするの……?