Why Large LIMIT Offsets Slow MySQL Queries and How to Fix Them

First, the MySQL version used is 5.7.17.

Table test has the following structure:

+--------+---------------------+------+-----+---------+----------------+
| Field  | Type                | Null | Key | Default | Extra          |
+--------+---------------------+------+-----+---------+----------------+
| id     | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| val    | int(10) unsigned    | NO   | MUL | 0       |                |
| source | int(10) unsigned    | NO   |     | 0       |                |
+--------+---------------------+------+-----+---------+----------------+

The table contains 5,242,882 rows, with val indexed but not unique.

Running a query with a large offset: SELECT * FROM test WHERE val=4 LIMIT 300000,5; takes about 15.98 seconds because MySQL must read 300,005 index leaf nodes and then fetch the same number of rows from the clustered index, discarding the first 300,000 rows.

A more efficient rewrite uses an inner join:

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 0.38 seconds, showing a clear performance gain.

Why the Difference Occurs

MySQL first scans the secondary index to locate the qualifying rows, then follows each primary‑key pointer to the clustered index to retrieve the full row. With a large OFFSET, it performs many unnecessary random I/O operations.

To verify, the buffer pool was examined. After running the slow query, the InnoDB buffer pool contained 4,098 data pages and 208 index pages for the test table. After the fast join query, only 5 data pages and 390 index pages were loaded, confirming far fewer page reads.

Buffer‑Pool Cleanup

To ensure a clean state on each MySQL restart, the options innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup should be disabled, preventing automatic dumping and loading of buffer‑pool contents.

References:

https://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/

https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-buffer-pool-tables.html