Fast Pagination in MySQL with Keyset Pagination

LIMIT 20 OFFSET 100000 looks innocent, but MySQL must read and throw away 100,000 rows to return 20. Deep pages get slower and slower.

The problem

SELECT id, title, created_at FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;   -- reads 100,020 rows

Keyset (seek) pagination

Remember where the last page ended, and continue from there:

-- First page
SELECT id, title, created_at FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Next page: last row seen had created_at = '2026-09-30 10:15:00', id = 8812
SELECT id, title, created_at FROM posts
WHERE (created_at, id) < ('2026-09-30 10:15:00', 8812)
ORDER BY created_at DESC, id DESC
LIMIT 20;

CREATE INDEX idx_posts_created_id ON posts (created_at DESC, id DESC);

Every page now reads only about 20 index entries, whether it's page 2 or page 5,000. Including id as a tie-breaker makes the order stable when timestamps are equal.

If the row-comparison form isn't using your index on an older version, the expanded form works everywhere:

WHERE created_at < :created_at OR (created_at = :created_at AND id < :id)

In the API

app.get('/posts', async (req, res) => {
  const cursor = req.query.cursor ? JSON.parse(Buffer.from(req.query.cursor, 'base64url')) : null;
  const [rows] = await db.query(
    `SELECT id, title, created_at FROM posts
     ${cursor ? 'WHERE (created_at, id) < (?, ?)' : ''}
     ORDER BY created_at DESC, id DESC LIMIT 21`,
    cursor ? [cursor.createdAt, cursor.id] : []
  );
  const hasMore = rows.length > 20;
  const page = rows.slice(0, 20);
  const last = page.at(-1);
  res.json({
    data: page,
    nextCursor: hasMore ? Buffer.from(JSON.stringify({ createdAt: last.created_at, id: last.id })).toString('base64url') : null,
  });
});

Trade-offs

• No "jump to page 57"; you move next/previous. Perfect for feeds and infinite scroll.
• If you need page numbers on huge tables, a "deferred join" helps: select only IDs with OFFSET from a covering index, then join to fetch the rows.