Why MySQL LIMIT offset slows down deep pagination and how to fix it

Two LIMIT execution methods

MySQL supports LIMIT offset, size and LIMIT size (which is equivalent to LIMIT 0, size). The only difference is whether the offset is zero.

Internal execution of LIMIT

MySQL consists of a server layer and a storage‑engine layer (usually InnoDB). The server executor calls the storage engine to retrieve rows that satisfy the query conditions and puts them into a result set for the client.

Running EXPLAIN SELECT * FROM page ORDER BY id LIMIT 0,10; shows that the key used is PRIMARY , meaning the primary‑key index is used.

The primary index is a B+ tree stored in InnoDB. Its leaf nodes contain the full row data, while a secondary (non‑primary) index leaf node only stores the primary‑key value; retrieving the full row then requires a "back‑table" lookup.

If the leaf node belongs to a primary index, it stores complete row information.

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

Execution based on the primary index

When LIMIT 0,10 is used, InnoDB directly returns the first 10 full rows from the primary B+ tree, which the server returns to the client.

When a large offset is used, 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. This means a lot of unnecessary data is read and copied, causing the query to be slower.

Therefore, LIMIT 6000000,10 is slower than LIMIT 10 because the engine has to fetch and discard many rows.

Optimizing large‑offset queries

One approach is to first fetch the starting id with a sub‑query that only returns the primary‑key column, then retrieve the desired rows using that id:

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

The sub‑query reads only the id column, reducing the amount of data copied. The outer query then uses the primary index to jump directly to the required row (logarithmic time) and fetch the next 10 rows.

Execution based on a secondary index

If the query orders by a non‑primary column, e.g. ORDER BY user_name, MySQL first reads the secondary index to obtain the primary keys, then performs a back‑table lookup for each row. With a small offset this works, but a large offset (e.g., 6,000,000) forces the optimizer to choose a full table scan because the cost of millions of back‑table lookups is higher.

This phenomenon is known as deep pagination . It has no perfect solution in MySQL or Elasticsearch; the usual mitigation is to limit the number of rows returned or to use id‑based pagination.

Practical recommendations

Prefer LIMIT size (or LIMIT 0, size) over LIMIT offset, size whenever possible.

For very large tables, avoid deep pagination by fetching data in batches using the primary‑key order (e.g., remember the last id and query WHERE id > last_id LIMIT batch_size).

If the use‑case is a user‑facing pagination UI, limit the maximum page number or switch to "next/previous" navigation or infinite scroll to keep offsets small.

When you must paginate on a non‑primary column, first retrieve only the primary keys with a sub‑query, then join back to the table to avoid excessive back‑table lookups.

Summary

LIMIT offset, size

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

Large offsets cause deep pagination problems; the only realistic mitigation is to limit result sets or use batch/id‑based retrieval.

Understanding the original business requirement often reveals that deep pagination is unnecessary; adjusting the UI or using a search engine can alleviate the issue.

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

References

"MySQL LIMIT clause underlying principles you must know" – https://blog.csdn.net/qq_34115899/article/details/120727513