Why a Large OFFSET in MySQL LIMIT Is Slow and How to Optimize It with a Join

First, the MySQL version used is 5.7.17 and the table test has three columns: id (bigint unsigned auto‑increment primary key), val (int unsigned indexed), and source (int unsigned).

The table contains about 5.2 million rows. When executing a query with a large LIMIT offset, row_count such as SELECT * FROM test WHERE val=4 LIMIT 300000,5; , MySQL must scan 300,005 index leaf nodes and then fetch the corresponding rows from the clustered index, discarding the first 300,000 rows, which takes around 16 seconds.

Rewriting the same logical request as a join— SELECT * FROM test a INNER JOIN (SELECT id FROM test WHERE val=4 LIMIT 300000,5) b ON a.id=b.id; —reduces the execution time to less than half a second because only the five needed rows are fetched from the clustered index.

The slowdown occurs because MySQL first reads the index leaf nodes, then for each leaf node accesses the clustered index to retrieve the full row, resulting in massive random I/O. Only the final five rows are needed, but the engine still performs 300,005 random reads.

To verify this behavior, the author examined the InnoDB buffer pool. After running the slow query, the buffer pool contained thousands of data pages (≈4098) and a few hundred index pages (≈208). After running the optimized join query, only five data pages and a few hundred index pages were loaded, confirming far fewer I/O operations.

The experiment involved clearing the buffer pool by shutting down MySQL, disabling innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup , and then running each query while checking information_schema.INNODB_BUFFER_PAGE for page counts.

The results demonstrate that the large‑OFFSET query pollutes the buffer pool with many rarely used pages, reducing overall cache efficiency. The article concludes that using a join or other techniques to avoid large offsets can dramatically improve performance and preserve buffer‑pool health.

Code examples:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.17    |
+-----------+
1 row in set (0.00 sec)

mysql> desc test;
+--------+---------------------+------+-----+---------+----------------+
| 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       |                |
+--------+---------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 5242882 |
+----------+
1 row in set (4.25 sec)

mysql> select * from test where val=4 limit 300000,5;
+---------+-----+--------+
| id      | val | source |
+---------+-----+--------+
| 3327622 | 4   | 4      |
| 3327632 | 4   | 4      |
| 3327642 | 4   | 4      |
| 3327652 | 4   | 4      |
| 3327662 | 4   | 4      |
+---------+-----+--------+
5 rows in set (15.98 sec)

mysql> select * from test a inner join (select id from test where val=4 limit 300000,5) b on a.id=b.id;
+---------+-----+--------+---------+
| id      | val | source | id      |
+---------+-----+--------+---------+
| 3327622 | 4   | 4      | 3327622 |
| 3327632 | 4   | 4      | 3327632 |
| 3327642 | 4   | 4      | 3327642 |
| 3327652 | 4   | 4      | 3327652 |
| 3327662 | 4   | 4      | 3327662 |
+---------+-----+--------+---------+
5 rows in set (0.38 sec)

-- Buffer‑pool page counts after slow query
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;
+------------+----------+
| index_name | count(*) |
+------------+----------+
| PRIMARY    | 4098     |
| val        | 208      |
+------------+----------+

-- Buffer‑pool page counts after optimized join query
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;
+------------+----------+
| index_name | count(*) |
+------------+----------+
| PRIMARY    | 5        |
| val        | 390      |
+------------+----------+

By avoiding large offsets, the query reduces unnecessary random I/O, speeds up execution, and prevents buffer‑pool pollution.