Investigating a slow pagination query

The starting query

SELECT id, created_at, title
FROM posts
WHERE status = 'published'
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 100000;

The concern is the large offset: rows before the requested page still have to be accounted for. The actual work and plan depend on available indexes, predicates, row distribution, and the optimizer.

Evidence to collect first

• Exact MySQL version, table definition, and all indexes.
• EXPLAIN output for shallow and deep pages.
• Representative row counts and distribution for status.
• Repeated latency and rows-read measurements under comparable conditions.
• Whether users need arbitrary page jumps or only next/previous navigation.

For index-ordering behavior see the MySQL ORDER BY optimization manual. A possible candidate index might begin with (status, created_at, id), but direction, selectivity, storage engine, workload, and existing indexes must be reviewed before testing.

A keyset form to evaluate

If the last row on the previous page has created_at = :last_created_at and id = :last_id, the next page can use a cursor predicate:

SELECT id, created_at, title
FROM posts
WHERE status = 'published'
  AND (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Confirm tuple comparison support and the exact ordering semantics for your target version. The unique id tiebreaker makes the order deterministic for equal timestamps. Rows inserted or updated between page requests can still affect a changing dataset; decide whether that behavior is acceptable or whether the application needs a snapshot boundary.

Tradeoffs and verification

Keyset pagination supports efficient continuation in a known order but does not naturally jump to an arbitrary page number. Check nullable sort keys, collation/time precision, cursor encoding, filter changes, and authorization boundaries. Preserve the existing query's result semantics before comparing performance.

1. Compare old and new result sets around ties and page boundaries.
2. Test first, middle, and deep pages with representative data.
3. Compare plans and repeated latency under the same test conditions.
4. Measure the candidate index's storage and write costs.

Until those measurements exist, this remains a hypothesis, not a result.