Why MySQL OFFSET Is So Slow and How to Fix It

Six years ago, the author noticed that a MySQL query with a large OFFSET (e.g., SELECT * FROM t_record WHERE age > 10 OFFSET 10000 LIMIT 10) took seconds to execute, even though the table only contained 50,000 rows and an index existed on age. The slowness stems from the fact that MySQL’s secondary indexes are B+‑trees, and finding the Nth row via OFFSET cannot use the tree’s branch‑filtering capability; the engine must walk the leaf‑node linked list, discarding the first N‑1 entries.

The B+‑tree’s leaf nodes form a linked list, so the optimizer ends up scanning 10,000 index entries, then for each entry performs a random I/O lookup on the clustered primary key to retrieve the full row, resulting in roughly 10,000 random I/Os.

Two optimization strategies are discussed:

Solution 1 – Keyset Pagination (skip OFFSET)

SELECT * FROM t_record WHERE id > last_id LIMIT 10

Advantages: uses the primary‑key index directly, avoiding the secondary‑index scan; reduces random I/O.

Disadvantages: only works when pagination can be based on the primary key and no additional filter (e.g., age) is required.

Solution 2 – Index Covering with Subquery

SELECT * FROM t_record WHERE id IN (
    SELECT id FROM t_record WHERE age > 10 OFFSET 10000 LIMIT 10
)

Here the subquery retrieves only the primary‑key values from the secondary index, so the engine does not need to fetch full rows for the discarded rows; only the final 10 rows trigger a lookup on the clustered index, cutting random I/Os dramatically.

Advantages: works with any WHERE condition, keeps pagination semantics, and greatly reduces I/O.

Disadvantage: the secondary index still walks the leaf‑node list, so the scan remains O(N) for the offset portion, but the overall cost is far lower.

In practice, if the product can switch to simple "previous/next" navigation without additional filters, Solution 1 is acceptable; otherwise, Solution 2 offers a more universal improvement.

Understanding the root cause of slow OFFSET queries not only helps solve the immediate performance issue but also deepens knowledge of MySQL’s storage engine, which is valuable for interviews and real‑world work.