This article reproduces a slow‑query scenario caused by deep pagination with LIMIT, explains the underlying index mechanics, and presents several practical SQL techniques—subquery, INNER JOIN, and anchor‑record methods—to dramatically reduce execution time.
GaussDB for MySQL introduces a Limit‑Offset pushdown optimization that moves offset calculation to the InnoDB storage engine, eliminating unnecessary row transfers and index‑back‑table lookups, thereby dramatically reducing query latency for large offsets as demonstrated by TPC‑H benchmarks.
This article explains why MySQL queries using LIMIT with a large offset become extremely slow, demonstrates the inefficiency through a 950‑million‑row table example, and shows how rewriting the query with a sub‑select join reduces execution time from seconds to milliseconds while preserving results.
A production MySQL query that uses huge OFFSET values caused massive slowdown, and the article explains the root cause, demonstrates data‑generation scripts, benchmarks the problem, and presents three practical solutions—including index‑covering subqueries, remembering the last primary‑key, and offset throttling—to dramatically improve pagination performance.
This article explains how using a large LIMIT offset on a 9.5‑million‑row MySQL table causes massive I/O and slow queries, and demonstrates a sub‑query‑based rewrite that fetches only primary keys before joining, dramatically reducing execution time while also showing buffer‑pool effects.
This article demonstrates how to dramatically improve MySQL LIMIT offset query performance by moving the filter into a subquery that selects only primary keys, then joining to retrieve full rows, and validates the approach with buffer pool statistics and execution time measurements.
This article demonstrates how rewriting a MySQL LIMIT OFFSET query to first fetch primary keys in a sub‑query and then join the main table can cut execution time from over 16 seconds to under one second by avoiding massive random I/O and buffer‑pool pollution.
