Optimizing MySQL LIMIT OFFSET Queries by Reducing Unnecessary Row Scans

The author presents a performance problem where a MySQL query with LIMIT 300000,5 on a table of over five million rows takes more than 15 seconds because MySQL must scan 300,005 index entries and fetch the corresponding clustered rows before discarding the first 300,000 rows.

To solve this, the query is rewritten so that the sub‑query selects only the primary keys that satisfy the condition and limit, and the outer query joins these keys back to the main table to retrieve the remaining columns. This reduces the number of random I/O operations dramatically.

-- Optimized SQL
SELECT  *
FROM    `table_name` main_table
RIGHT   JOIN (
    SELECT id
    FROM   `table_name`
    WHERE
conditions
LIMIT 0,10
) temp_table ON temp_table.id = main_table.id;

Experimental results show the original query taking 16 s (execution) + 107 ms (fetching) while the optimized version completes in 347 ms (execution 163 ms, fetching 184 ms). Further verification using InnoDB buffer‑pool statistics confirms that the optimized query loads only a handful of data pages, whereas the original query loads thousands.

The article also discusses how excessive loading of rarely‑used pages pollutes the buffer pool, and recommends disabling innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup to ensure a clean buffer pool after each restart.

References are provided for deeper reading on MySQL ORDER BY LIMIT performance and InnoDB information‑schema buffer‑pool tables.