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

Background

A single table (no sharding) contains about 9.5 million rows. An original pagination query using LIMIT took roughly 167 seconds (execution 16.8 s, fetching 107 ms). After applying an optimization the same query completed in 347 ms (execution 163 ms, fetching 184 ms).

MySQL Version and Table Schema

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)

The id column is an auto‑increment primary key; val is a non‑unique index.

Data Volume

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

Problem with Large OFFSET

When the offset in LIMIT offset, row_count is large, MySQL must scan the index leaf nodes up to the offset, then fetch the corresponding rows from the clustered index, and finally discard the first offset rows. 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 (26.19 sec)

This query required MySQL to read 300 005 index leaf entries and 300 005 clustered‑index rows, only to discard the first 300 000 rows, causing massive random I/O.

Optimized Approach Using Sub‑Select Join

Rewrite the query so that the sub‑select retrieves only the primary keys of the desired rows, then join back to fetch the full columns. This limits index lookups to the exact number of needed rows.

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.09 sec)

The execution time drops from dozens of seconds to a fraction of a second because only five index entries and five data pages are accessed.

Buffer Pool Evidence

Before the optimization, the InnoDB buffer pool contained thousands of data pages:

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      |
+------------+----------+
2 rows in set (0.04 sec)

After the optimized query, only the pages needed for the five rows remain:

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      |
+------------+----------+
2 rows in set (0.03 sec)

Why the Difference Occurs

The first query loads many rarely‑used pages into the buffer pool, polluting it and increasing I/O for subsequent queries. The second query touches only the necessary pages, keeping the buffer pool clean.

Ensuring a Clean Buffer Pool on Restart

To guarantee the buffer pool is empty after each MySQL restart, disable the options innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup. This prevents the pool from being repopulated with stale pages.

Illustrations

Typical index‑leaf scan for a large offset:

Optimized access pattern fetching only needed rows:

These diagrams illustrate how the optimized query avoids scanning unnecessary index entries.

Conclusion

Using LIMIT with a large offset on a MySQL table forces the engine to read a huge number of index and data pages, leading to severe performance degradation. Rewriting the query to first select the primary keys of the required rows and then joining back dramatically reduces I/O, execution time, and buffer‑pool pollution.