Understanding MySQL LIMIT OFFSET Performance and Buffer‑Pool Impact

In this post the author, a senior architect, investigates why a MySQL query with a large LIMIT offset, row_count clause becomes inefficient when the offset is huge. Using a table test (id BIGINT primary key, val INT indexed, source INT) populated with over five million rows, the author first runs the naive query:

mysql> select * from test where val=4 limit 300000,5;

The execution takes about 16 seconds and scans 300,005 index leaf nodes and the same number of clustered index rows, loading thousands of data pages into the InnoDB buffer pool.

To improve performance, the query is rewritten as an inner‑join that first selects the required primary‑key ids:

mysql> select * from test a inner join (select id from test where val=4 limit 300000,5) b on a.id=b.id;

This version finishes in under 0.4 seconds because it only accesses the five needed rows, loading just five data pages.

The author validates the hypothesis by checking the InnoDB buffer‑pool page counts before and after each query using:

mysql> select index_name, count(*)
       from information_schema.INNODB_BUFFER_PAGE
       where INDEX_NAME in ('val','primary')
         and TABLE_NAME like '%test%'
       group by index_name;

Before the first query the buffer pool contains 4,098 data pages and 208 index pages; after the optimized query it contains only 5 data pages and 390 index pages, confirming the reduction in unnecessary I/O.

The article also notes that leaving many rarely‑used pages in the buffer pool can pollute it, and suggests disabling innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup to ensure a clean buffer pool on restart.

References: MySQL ORDER BY LIMIT performance InnoDB Buffer Pool Information Schema