Performance impact of MySQL LIMIT … OFFSET on large tables

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?