Why Does MySQL LIMIT offset,size Slow Down With Large Offsets?

Two LIMIT Execution Processes

MySQL supports two forms of the LIMIT clause: LIMIT offset, size and LIMIT size. The latter is equivalent to LIMIT 0, size, i.e., it always starts from the first row.

Internal Execution Logic of LIMIT

MySQL consists of a server layer and a storage‑engine layer (usually InnoDB). The server’s executor calls the storage engine to fetch rows; when rows satisfy all conditions they are placed into the result set and returned to the client.

Running EXPLAIN SELECT * FROM page ORDER BY id LIMIT 0,10 shows that the query uses the PRIMARY key, i.e., the primary‑key index.

The primary‑key index is a B+ tree stored inside InnoDB. Its leaf nodes contain complete row data, while a secondary index’s leaf nodes contain only the primary‑key values. Accessing a secondary index therefore requires a “back‑lookup” (回表) to the primary index to retrieve full rows.

If the leaf node belongs to the primary index, it stores the full row.

If it belongs to a secondary index, it stores only the primary key; the engine must fetch the full row from the primary index (back‑lookup).

Execution with a Primary‑Key Index

For SELECT * FROM page ORDER BY id LIMIT 0,10, the server asks InnoDB to return the first 10 rows from the primary‑key B+ tree. All columns are returned directly, so the query is fast.

When the offset is large, e.g., LIMIT 6000000,10, InnoDB still reads the first 6,000,010 rows, then the server discards the first 6,000,000 rows and keeps the last 10. The extra rows are useless but still cost I/O and CPU, making the query much slower.

Execution with a Secondary Index

For SELECT * FROM page ORDER BY user_name LIMIT 0,10, InnoDB first reads the secondary index to obtain the primary‑key values, then performs a back‑lookup for each row. If the offset is non‑zero, the engine still discards the preceding rows, and the back‑lookup cost adds up.

When the offset becomes huge (e.g., 6 million), the optimizer may decide that a full table scan (type=ALL) is cheaper than scanning the secondary index millions of times, which leads to a severe performance hit.

Deep Pagination Problem

When the offset grows to hundreds of thousands or millions, the query is called “deep pagination”. MySQL (and even Elasticsearch) cannot efficiently skip that many rows; the only practical mitigations are to limit the maximum page number or to fetch data in batches using the primary key.

Batch Retrieval Using Primary Key

Instead of LIMIT offset, size, repeatedly query

SELECT * FROM page WHERE id >= last_id ORDER BY id LIMIT 100

. Each batch returns a small, stable number of rows regardless of the total table size.

Optimized Query for Large Offsets

One can first fetch the starting primary‑key with a subquery that only selects the id column, then retrieve the desired rows:

SELECT * FROM page WHERE id >=(
    SELECT id FROM page ORDER BY id LIMIT 6000000,1)
ORDER BY id LIMIT 10;

This reduces the amount of data copied during the first step to a single column, and the second step uses the primary‑key index to jump directly to the target row, roughly halving the execution time.

Summary

LIMIT offset, size

is slower than LIMIT size; the larger the offset, the slower the query.

Very large offsets cause the “deep pagination” problem, often forcing a full table scan.

Mitigation strategies include limiting the maximum page number, using primary‑key‑based batch queries, or redesigning the UI to avoid deep pagination.

For small tables (e.g., <1k rows) the simple LIMIT offset, size approach is acceptable.