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

The article begins by showing the MySQL version (5.7.17) and the schema of a table test with an auto‑increment primary key id , a non‑unique index column val , and a source column.

After inserting about 5.2 million rows, a query that uses LIMIT 300000,5 on val=4 takes roughly 16 seconds, while an equivalent query rewritten with an inner join on a sub‑select of the primary keys finishes in under half a second.

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 |
+---------+-----+--------+  (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 |
+---------+-----+--------+---------+  (0.38 sec)

The slowdown is explained by the way MySQL processes the first query: it must traverse 300,005 leaf‑node entries of the secondary index, then for each entry fetch the full row from the clustered primary index, resulting in 300,005 random I/O operations, most of which are discarded.

To verify this, the author inspects the InnoDB buffer pool before and after each query, counting pages belonging to the val index and the primary key. The plain LIMIT query loads thousands of data pages (e.g., 4,098 primary pages and 208 index pages), whereas the join version loads only the five pages needed for the final rows.

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   |
+-----------+----------+

The experiment demonstrates that large OFFSET values cause buffer‑pool pollution by loading many rarely‑used pages, which can degrade overall performance.

To avoid this side effect on server restarts, the article recommends disabling innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup , which control dumping and reloading 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