Question
Performance impact of MySQL LIMIT … OFFSET on large tables
Ari Maple
0 reputation · 17 Nov 2022, 02:36 UTC
122.2K views
Loading…
0 reputation · 17 Nov 2022, 02:36 UTC
The goal is to determine whether MySQL should modify the LIMIT … OFFSET clause so that deep pagination does not require the server to read and discard all preceding rows, thereby reducing latency and resource consumption in user‑facing applications.
In MySQL 8.0 the optimizer can push a plain LIMIT down to InnoDB, but the presence of OFFSET prevents early row termination, causing the engine to scan up to the offset point each time; no optimizer hint or session variable currently exists to bypass this behavior, and changing it raises compatibility concerns for existing queries that rely on OFFSET‑based paging.
Should MySQL deprecate OFFSET‑based pagination in favor of keyset pagination for new development? Is there a feasible optimizer hint or session variable that could allow LIMIT … OFFSET to skip preceding rows without breaking existing semantics? What migration path would be needed to preserve compatibility while encouraging the use of more efficient pagination patterns?
33275 reputation · 17 Nov 2022, 13:45 UTC
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:
mysql-- keyset pagination (assuming `id` is the ordered, unique column)
SELECT * FROM t
WHERE id > :last_seen_id
ORDER BY id
LIMIT :page_size;
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.
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.
Use comments to ask for clarification. Post a solution as an answer.
33,275 reputation · 17 Nov 2022, 03:44 UTC
The existing answer correctly shows keyset pagination using WHERE id > :last_seen_id, but this only works reliably when the ORDER BY column is unique (e.g., a primary key). If the ordering column can contain duplicates — such as ORDER BY created_at — a simple > predicate can skip rows that share the same value as the last-seen row.
To handle non-unique ordering, extend the WHERE clause with a tiebreaker on a unique column:
SELECT * FROM t
WHERE (created_at, id) > (:last_created_at, :last_id)
ORDER BY created_at, id
LIMIT :page_size;MySQL supports row-value comparisons since 8.0, so this syntax is valid and lets the optimizer use a composite index on (created_at, id) for an efficient seek. Without the tiebreaker, deep pagination can silently miss or duplicate rows when data changes between pages.