Why LIMIT … OFFSET slows down on large tables
Likely explanation: When a query contains LIMIT … OFFSET, MySQL must read and discard the first OFFSET rows that satisfy the WHERE and ORDER BY clauses before it can return the LIMIT block. Even if an index matches the ORDER BY, the storage engine still scans through those skipped rows, so the work grows roughly linearly with the offset value.
Confirmed facts:
- EXPLAIN (or EXPLAIN ANALYZE in MySQL 8.0+) shows the same "rows examined" regardless of the LIMIT value; only the OFFSET changes the amount of work.
- Benchmarking on tables with >1 M rows shows response time roughly doubling when the OFFSET doubles, indicating a scan‑cost dominated operation.
- The optimizer can push a plain LIMIT down to InnoDB, but the presence of OFFSET prevents early row termination.
Steps to mitigate the impact
- Replace OFFSET‑based pagination with keyset (seek) pagination when you can define a stable, unique ordering column. Example:
-- keyset pagination (assuming `id` is the ordered, unique column)
SELECT * FROM t
WHERE id > :last_seen_id
ORDER BY id
LIMIT :page_size;
- Ensure the column(s) used in the ORDER BY clause are indexed (preferably a covering index) so the seek can be satisfied without extra sorting or file‑sort.
Missing diagnostic detail
To confirm whether keyset pagination can be applied without added cost, we need to know:
Which column(s) appear in the ORDER BY clause of your paginated query, and are those columns covered by an index?
If the ORDER BY column(s) are not indexed, adding an appropriate index will reduce the scan cost but will not eliminate the OFFSET overhead; keyset pagination would then require the new index to be effective.
Feasibility of an optimizer hint or session variable
As of MySQL 8.0 there is no optimizer hint, session variable, or optimizer_switch flag that changes the semantics of LIMIT … OFFSET to skip preceding rows without reading them. Introducing such a feature would break existing queries that depend on the current skip‑and‑discard behavior, so any change would need a deprecation period and explicit opt‑in.
Migration path for compatibility
- Document keyset pagination as the preferred pattern for new development.
- Provide application‑level helpers that generate the seek condition from the last‑seen key values.
- Leave existing OFFSET‑based queries untouched; they will continue to work, albeit with the known performance characteristic.
- Monitor slow‑query logs for high‑OFFSET queries and refactor them opportunistically.
mysql