PLANETSCALE 2023-04-18 Tier 3

PlanetScale — Pagination in MySQL¶

Pedagogical PlanetScale post by Aaron Francis (2023-04-18, re-fetched 2026-04-21). This is the pedagogical parent of the PlanetScale pagination corpus on the wiki — the canonical first-principles walk-through from which the FastPage launch post (Mike Coutermarsh, 2022-08-16) derives its mechanism and citations. Francis's 2022 blog post Efficient Pagination Using Deferred Joins was already cited on the wiki by the FastPage entry; this PlanetScale article is the broader companion piece that introduces the whole pagination problem space — deterministic ordering, offset/limit mechanics and drift, deferred joins as the in-place optimisation, cursor/keyset pagination as the architectural alternative, and composite cursors for multi-column sort orders.

Summary¶

Francis walks the reader through four load-bearing primitives of pagination on MySQL:

Deterministic ordering is a prerequisite to correct pagination. ORDER BY first_name over [Aaron Francis, Aaron Smith, Aaron Jones] is allowed to return any of the three permutations — MySQL has no obligation to pick one consistently. The standard fix is to append the primary key: ORDER BY first_name, id. Without deterministic ordering, both offset and cursor pagination can skip or duplicate rows, not as a drift artefact but because the database's definition of "page N" is itself unstable.
Offset/limit pagination is easy to implement and directly addressable (page links, jump-to-page-47). But OFFSET M LIMIT N reads and discards the first M rows — the database never skips work, it just throws it away. At deep pages this becomes multi-second (offset pagination cost canonicalises this).
The deferred-join rewrite sidesteps most of the cost: walk the index-only subquery with OFFSET, then INNER JOIN the full rows back in. The engine still walks M + N index entries but only hydrates N full rows — the expensive part collapses from O(M+N) to O(N) while preserving offset UX.
Cursor pagination replaces OFFSET M with WHERE (sort_key) > last_seen — the database seeks to the boundary rather than walking past discarded rows. For a multi-column sort the cursor must include every sort column, and the predicate becomes a lexicographic OR-chain: (first_name > 'Aaron') OR (first_name = 'Aaron' AND id > 25995). Cursor pagination is O(1) at any depth but cannot jump to an arbitrary page and requires the client to carry state.

The post also canonicalises the two major resilience properties that differ between the approaches:

Under concurrent row deletion, offset pagination drifts — the Sonya-Dickens example: if row 2 is deleted while the user reads page 1, page 2 starts one row too late and the user never sees Sonya. Cursor pagination is immune because the cursor is the last-seen row's identity, not its position. (concepts/pagination-drift canonicalises this.)
Under concurrent row insertion (not addressed by Francis but a natural extension) the same asymmetry holds — offset pages duplicate, cursor pages are stable.

Key takeaways¶

Deterministic ordering is a prerequisite, not a tuning knob. ORDER BY first_name is not an ordering at all in the relational sense when first_name has duplicates — it's a partial order, and MySQL is free to choose any total order that extends it. The canonical fix is to append the primary key: ORDER BY first_name, id. This applies equally to both offset and cursor pagination. (Source: this post, §1.)
OFFSET is a discard-count operator, not a seek-key operator. Francis's framing is load-bearing for why offset pagination degrades: "The way that the OFFSET keyword works is that it discards the first n rows from the result set. It doesn't simply skip over them. Instead, it reads the rows and then discards them." Very deep pages can "take multiple seconds to load." (Source: this post, §2; canonicalised on concepts/offset-pagination-cost.)
Offset pagination drifts under concurrent deletes. Francis's Sonya-Dickens worked example: user views page 1 ending on "Judge Bins" at id 10, Sonya Dickens at id 11 is supposed to be first on page 2. A concurrent delete of id 2 shifts all rows up by one position, so Sonya slides onto page 1 (where the user has already moved past) and page 2 now starts at Hope Streich. The user never sees Sonya. "Sorry Sonya. Your user never sees her unless they navigate back to page one." Canonical wiki home: concepts/pagination-drift. (Source: this post, §2.)
The deferred-join rewrite is presented here with both forms. The nested-subquery form from High Performance MySQL — "SELECT * FROM people INNER JOIN (SELECT id FROM people ORDER BY first_name, id LIMIT 10 OFFSET 450000) AS tmp USING (id) ORDER BY first_name, id" — is the primary example; the graph from the FastPage post is embedded inline. Francis attributes the Rails port to FastPage and the Laravel port to Hammerstone's fast-paginate. Francis is named by the FastPage post as the deferred-join technique's popularising voice; he is also the canonical author of Efficient Pagination Using Deferred Joins (2022). (Source: this post, §3; cross-ref concepts/deferred-join.)
Cursor pagination is O(1) at any depth because the cursor is a seek-key, not a skip-count. "Instead of using the OFFSET keyword, we use the cursor to construct a WHERE clause that filters out all the rows that the user has already seen." Cursor-paginated queries "can be much more performant than offset/limit simply because it accesses much less data. Instead of generating a result set and throwing away everything before the offset, the database can start at the offset and return the next N records." (Source: this post, §4; canonicalised on concepts/cursor-pagination.)
For multi-column sorts, cursors must encode every sort column as a lexicographic OR-chain. Francis's canonical example with ORDER BY first_name, id and last-seen row (first_name='Aaron', id=25995):

WHERE (
  (first_name > 'Aaron')                    -- Names after Aaron
  OR
  (first_name = 'Aaron' AND id > 25995)     -- Aarons, but after the last id seen
)
ORDER BY first_name, id
LIMIT 10

The general rule: "As you add more columns to the sort order, you'll need to add more filters to the WHERE clause." Canonicalised on concepts/composite-cursor. (Source: this post, §4.)

Cursor pagination is immune to the delete-drift failure mode — even when the cursor points to a deleted row. "This is true even if the cursor is pointing to a record that was deleted. If the cursor points to a record that was deleted, we're still telling the database, 'the last record I saw was ID 10, and I want to see the next ten records.' Again, the database doesn't care that the record was deleted. It just knows that the next record is Sonya Dickens." The cursor is a position in the sort order's key space, not a foreign-key reference. Canonical resilience property of keyset pagination. (Source: this post, §4; cross-ref concepts/cursor-pagination + concepts/pagination-drift.)
The UX trade is load-bearing in the choice. Offset pagination gives "directly addressable pages" — users can "navigate from page 1 directly to page 10". Cursor pagination "is impossible to address a specific page directly" because "there is no way to create a cursor without knowing the last record that has been seen. You can only navigate to the next page." Francis's framing is explicit: "Which method you choose is up to you." This is the UX-vs-performance trade axis that makes the deferred-join rewrite so valuable — it preserves the UX while reducing (not eliminating) the cost. (Source: this post, closing section.)

Operational numbers¶

Deferred-join benchmark graph is embedded — the same FastPage chart from Coutermarsh's launch post: ActiveRecord baseline climbs steeply across 2,000 pages while the deferred-join line stays near-flat. No new numbers are added here — the FastPage benchmark is the cited evidence. See concepts/offset-pagination-cost for the quantified 1,228.7 ms → 457.3 ms @ offset 100 datum.
"Multi-second" is Francis's qualitative handle for deep offset pages — a pedagogical framing rather than a measured datum, reinforced by the FastPage post's quantitative sweep.

Caveats¶

Pedagogy voice, not production-incident retrospective. No customer numbers, no PlanetScale-specific internals (the post is MySQL-generic, not Vitess-specific); the running example is a toy people table.
No INSERT-drift framing. Francis walks through delete drift but not insert drift — both fail modes exist in offset pagination but Francis covers only the deletion case. The concepts/pagination-drift page generalises.
No indexing prerequisites deep-dive. Francis closes with "You will need to consider a proper indexing strategy to ensure the database can efficiently find the necessary records" but does not elaborate. The practical requirement — a composite index matching the sort order — is load-bearing for both cursor pagination O(1) and deferred join's index-only scan; canonicalised on the deferred-join and cursor-pagination concept pages.
Cursor-encoding elided. Francis uses bare (id=10) / (first_name=Aaron, id=25995) tuples in examples with a "usually it would be base64 encoded" aside. Real-world cursor APIs (Relay, Stripe, GitHub) serialise cursors opaquely — the encoding choice is orthogonal to the mechanism but has its own trade-offs (opacity prevents clients from synthesising cursors; versioning lets the server evolve the schema).
No SERIALIZABLE / snapshot-isolation framing. Offset drift under deletes is a pagination-level consistency issue that a stronger isolation level could paper over (REPEATABLE READ on a single connection would see a stable snapshot), but Francis doesn't engage this. The mainstream use case is stateless per-request pagination where session-level snapshots are unavailable.
Re-fetch date drift: 2023-04-18 original publication re-fetched on 2026-04-21. Author has since left PlanetScale (Francis was already an independent voice at post time but the PlanetScale-hosted URL persists).
URL slug truncation: the raw filename slug is 2026-04-21-pagination-in-mysql-966e46e0.md; the URL itself is https://planetscale.com/blog/mysql-pagination — canonical slug on PlanetScale's blog, no truncation in the URL.

Source¶

Original: https://planetscale.com/blog/mysql-pagination
Raw markdown: raw/planetscale/2026-04-21-pagination-in-mysql-966e46e0.md