Optimizing MySQL Pagination Queries with Subqueries and Buffer‑Pool Analysis

This article starts with a real‑world example where a financial ledger table containing 9.5 million rows is queried with SELECT * FROM test WHERE val=4 LIMIT 300000,5 , taking over 16 seconds because MySQL must scan 300,005 index entries and corresponding clustered rows before discarding the first 300,000 rows.

The author proposes an optimization: move the LIMIT clause into a subquery that only selects the primary‑key IDs, then join the original table on those IDs. The rewritten query SELECT a.* FROM test a INNER JOIN (SELECT id FROM test WHERE val=4 LIMIT 300000,5) b ON a.id=b.id reduces execution time to under 0.5 seconds.

The underlying principle is to avoid the costly "row‑by‑row" random I/O caused by large offsets. By first locating the needed IDs, MySQL performs far fewer index lookups and clustered‑index accesses.

To prove the hypothesis, the author runs experiments measuring InnoDB buffer‑pool page usage. After executing the original slow query, the buffer pool contains thousands of data pages (e.g., 4,098) and hundreds of index pages. After executing the optimized subquery‑join, only five data pages are loaded, confirming the reduction in unnecessary I/O.

The article also discusses how large‑offset queries can pollute the buffer pool, reducing cache efficiency for hot data. It recommends disabling innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup to ensure a clean buffer pool on each MySQL restart.

Finally, the author provides a summary of the experiment steps, including restarting MySQL, clearing the buffer pool, and comparing the buffer‑pool statistics before and after each query.