Why LIMIT with Large Offsets Slows MySQL Queries and How to Optimize It

This article investigates the performance issue caused by MySQL queries that use LIMIT offset, count with a large offset, showing that a query scanning 9555695 rows took 16 s while an optimized version took only 347 ms.

Problem statement : A financial transaction table with 5.2 million rows is queried with SELECT * FROM test WHERE val=4 LIMIT 300000,5. The original query scans 300 005 index entries and corresponding clustered rows, resulting in heavy random I/O and a 15.98 s execution time.

Optimization approach : Move the filter into a sub‑query that selects only the primary key IDs, then join back to retrieve the full rows:

SELECT  各种字段
FROM `table_name` main_table
RIGHT JOIN (
  SELECT  子查询只查主键
  FROM `table_name`
  WHERE 各种条件
  LIMIT 0,10
) temp_table ON temp_table.主键 = main_table.主键;

This reduces the number of random I/O operations to the number of rows actually needed (5), cutting execution time to 0.38 s.

Technical explanation : With a large offset, MySQL must traverse the index leaf nodes for every row up to the offset, then perform a “row‑lookup” on the clustered index for each, discarding the first 300 000 rows. This results in many unnecessary data‑page reads and pollutes the InnoDB buffer pool.

Experimental verification : The author runs two queries—one with the original LIMIT and one with the sub‑query join—while monitoring information_schema.INNODB_BUFFER_PAGE. The original query loads 4 098 data pages and 208 index pages into the buffer pool; the optimized query loads only 5 data pages and 390 index pages, confirming the reduction in I/O.

Additional notes : To avoid buffer‑pool contamination across restarts, the settings innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup should be disabled.

Conclusion : Rewriting large‑offset LIMIT queries to first fetch primary keys in a sub‑query and then join back eliminates unnecessary scans, dramatically improves performance, and preserves buffer‑pool efficiency.