Why MySQL LIMIT with Large Offsets Is Slow and How to Optimize It

In this article a senior architect explains why using LIMIT offset, count with a large offset in MySQL can be extremely slow, showing an example where a query took 16 s before optimization and only 347 ms after.

The root cause is that MySQL must scan the offset rows, reading index leaf nodes and the corresponding clustered index rows, then discard the unwanted rows, leading to massive random I/O and buffer‑pool pollution.

To avoid this, the author suggests moving the filtering condition into a sub‑query that first selects only the primary‑key values, then joining those keys back to fetch the required columns. This reduces the number of index and data page accesses to the number of rows actually needed.

-- Optimized SQL
SELECT  various_fields
FROM `table_name` main_table
RIGHT JOIN (
    SELECT id
    FROM `table_name`
    WHERE ... 
    LIMIT 300000,5
) temp_table ON temp_table.id = main_table.id;

Experimental verification using InnoDB buffer‑pool statistics shows that the original query loads thousands of data pages, while the optimized version loads only a handful, confirming the performance gain.

The article also notes that to keep the buffer pool clean after each MySQL restart, the options innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup should be disabled.

References: explainextended.com article on LIMIT performance and MySQL 5.7 documentation on InnoDB buffer‑pool information schema.