Skip to content

CONCEPT Cited by 1 source

Deferred join

A deferred join is a SQL rewrite technique that replaces a wide SELECT col1, col2, … FROM t ORDER BY k LIMIT N OFFSET M (which must hydrate M+N full rows from the clustered index) with an inner index-only query that selects only primary keys in the correct order, followed by an outer join that hydrates rows by primary key:

-- Baseline (slow at depth):
SELECT * FROM posts ORDER BY created_at DESC LIMIT 50 OFFSET 10000;

-- Deferred join:
SELECT * FROM posts
INNER JOIN (
  SELECT id FROM posts
  ORDER BY created_at DESC
  LIMIT 50 OFFSET 10000
) AS lim USING(id);

The rewrite is documented in High Performance MySQL (Schwartz / Zaitsev / Tkachenko) and popularised in 2022 by Aaron Francis's blog post Efficient Pagination Using Deferred Joins.

(Source: sources/2026-04-21-planetscale-introducing-fastpage-faster-offset-pagination-for-rails-apps.)

Why it works

The inner query — SELECT id FROM posts ORDER BY created_at DESC LIMIT 50 OFFSET 10000 — is an index-only scan. If created_at is indexed, the InnoDB secondary index already contains both the sort key (created_at) and the primary key (id) in every leaf node. The engine walks 10,050 index entries, picks the 50 surviving ids, and returns them without ever touching the clustered index.

Verbatim from High Performance MySQL, as quoted by Mike Coutermarsh:

This technique works "because it lets the server examine as little data as possible in an index without accessing rows."

The outer query does exactly 50 primary-key point lookups — one clustered-index descent per returned row — to hydrate full rows. Compared to the baseline:

Phase Baseline Deferred join
Secondary-index walks 10,050 10,050 (inner)
Clustered-index descents 10,050 50 (outer)
Full rows hydrated 10,050 50
Rows returned 50 50

The number of expensive clustered-index descents collapses from M + N to just N. The secondary-index walk is still O(M + N) in both, but it's hundreds of times cheaper per entry because it doesn't materialise row bodies.

The 2.7× benchmark

Coutermarsh's benchmark on a ~1M-row Rails Post table (both owner and created_at indexed):

# Baseline:
Post Load (1228.7ms) SELECT posts.* FROM posts
  ORDER BY posts.created_at DESC LIMIT 25 OFFSET 100

# With deferred join (via fast_page):
Post Pluck (456.9ms) SELECT posts.id FROM posts
  ORDER BY posts.created_at DESC LIMIT 25 OFFSET 100
Post Load (0.4ms) SELECT posts.* FROM posts
  WHERE posts.id IN (1271528, 1271527, …) ORDER BY posts.created_at DESC

1,228.7 ms → 457.3 ms at offset 100. A 2000-page sweep shows the deferred-join line staying near-flat while the baseline climbs steeply — the gap widens superlinearly with page depth.

Preconditions for the win

The optimisation depends on several properties holding:

  1. ORDER BY must be indexed. Without an index matching the sort order, the inner query can't be index-only — it has to sort, which defeats the purpose. For queries with WHERE predicates, a composite index on (predicate_col, order_col) is often the correct shape.
  2. Clustered-index storage. The optimisation specifically exploits the InnoDB-style architecture where secondary index leaves point to primary keys and full-row fetches require a second B+tree descent. Engines that store row pointers directly in secondary indexes (legacy MyISAM, some heap-organised tables) see smaller wins.
  3. Wide SELECT * with narrow sort/filter columns. If the SELECT list is already narrow enough to be covered by the existing index, the baseline is already fast — there's nothing to defer. The win scales with row size.
  4. Non-trivial OFFSET. For OFFSET 0…50 the two-round- trip overhead dominates the saved hydration cost. The optimisation is a loss at shallow depth — apply conditionally past a measured crossover threshold.

Implementation alternatives

Two equivalent SQL shapes — both are "deferred join" and query planners typically produce similar plans:

-- Subquery in IN-list (what fast_page emits):
SELECT * FROM posts
WHERE id IN (SELECT id FROM posts ORDER BY created_at DESC LIMIT 50 OFFSET 10000)
ORDER BY created_at DESC;

-- Explicit INNER JOIN (what High Performance MySQL shows):
SELECT * FROM posts
INNER JOIN (SELECT id FROM posts ORDER BY created_at DESC LIMIT 50 OFFSET 10000) AS lim
  USING(id);

The PlanetScale fast_page gem emits the IN (...) form as two separate SQL round-trips rather than one nested query: first pluck the ids, then pass them as a Ruby array into a WHERE id IN (…) clause. This costs one extra client-to- database round-trip but:

  • Keeps the two queries independent in the slow-query log (observability).
  • Avoids some edge cases where the MySQL planner de-optimises the nested subquery.

Comparison to cursor pagination

Axis Deferred join Cursor pagination
Skip-to-page-N UX Preserved Lost (next / previous only)
Cost at page N O(N) index walk O(1)
Cost constant Small (index-only) Smallest
Requires stable sort key No Yes (ties must be broken)
Works with arbitrary ORDER BY If indexed Yes
Client-side state None Must carry last-seen cursor

For next / previous UX, cursor is strictly better. Deferred join is the offset-preserving middle ground for applications that need arbitrary-page random access.

Seen in

Last updated · 378 distilled / 1,213 read