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

A table with about 9.5 million rows was queried with LIMIT 300000,5, taking roughly 167 seconds (execution 16 s, fetching 107 ms). After applying the optimization shown below, the same query finished in about 347 ms (execution 163 ms, fetching 184 ms).

-- Original (slow) SQL
SELECT  /* various fields */
FROM `table_name`
WHERE /* various conditions */
LIMIT 0,10;

-- Optimized (fast) SQL
SELECT  /* various fields */
FROM `table_name` main_table
RIGHT JOIN (
    SELECT /* sub‑query only primary key */
    FROM `table_name`
    WHERE /* various conditions */
    LIMIT 0,10
) temp_table ON temp_table.id = main_table.id;

The MySQL version used is 5.7.17:

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

The table structure (simplified) is:

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)

When the offset in LIMIT is large, MySQL must read the index leaf nodes one by one, then fetch the corresponding rows from the clustered index, resulting in massive random I/O. For example:

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)

Re‑writing the query to first fetch the primary keys and then join eliminates the unnecessary scans:

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)

The difference is clear: the first query loads 4 098 data pages and 208 index pages into the InnoDB buffer pool, while the second loads only 5 data pages and 390 index pages, as shown by the following buffer‑pool queries.

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;
+------------+----------+
| index_name | COUNT(*) |
+------------+----------+
| PRIMARY    | 4098     |
| val        | 208      |
+------------+----------+

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;
+------------+----------+
| index_name | COUNT(*) |
+------------+----------+
| PRIMARY    | 5        |
| val        | 390      |
+------------+----------+

These results confirm that the original query reads a huge number of irrelevant rows (the 300 000 offset) and pollutes the buffer pool, whereas the optimized version touches only the rows that are actually needed.

To avoid buffer‑pool pollution across restarts, disable the options innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup so that the pool is cleared on shutdown and not re‑loaded on startup.

Illustrations of the two execution paths:

In summary, using a large LIMIT offset forces MySQL to perform many random I/O operations on both index and data pages, dramatically slowing the query. By first selecting the primary keys (or using an inner join sub‑query) you limit the I/O to only the needed rows, achieving orders‑of‑magnitude speed‑ups and keeping the buffer pool clean.