Why MySQL LIMIT with Large OFFSET Is Slow and How to Speed It Up

A financial flow table with 9,555,695 rows was queried using LIMIT, which initially took 16 s 938 ms. After applying the optimization described below, the same query completed in 347 ms.

Operation: Put the filter condition into a subquery that selects only the primary‑key IDs, then join the subquery result with the main table to retrieve the remaining columns.

Principle: Reduce the number of table‑row lookups. This follows the recommendation in Alibaba’s Java Development Manual (Chapter 5, MySQL, rule 7) to use delayed association or subqueries for heavy pagination scenarios.

MySQL does not skip rows directly; it reads offset + N rows, then discards the first offset rows. When offset is large, the operation becomes very inefficient because many rows are read only to be thrown away.

Example of the original and optimized SQL:

-- Optimized before SQL
SELECT * FROM `table_name` WHERE ... LIMIT 0,10;

-- Optimized after SQL
SELECT a.* FROM `table_name` main_table
RIGHT JOIN (
    SELECT id FROM `table_name` WHERE ... LIMIT 0,10
) temp_table ON temp_table.id = main_table.id;

MySQL version used: 5.7.17.

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

Data volume: 5,242,882 rows.

Slow query example (large offset): SELECT * FROM test WHERE val=4 LIMIT 300000,5; Execution time: ~16 s.

Optimized query using a subquery:

SELECT a.* FROM test a
INNER JOIN (
    SELECT id FROM test WHERE val=4 LIMIT 300000,5
) b ON a.id = b.id;

Execution time: ~0.38 s.

The difference is explained by the way MySQL accesses index leaf nodes and the clustered index. The original query scans 300,005 index leaf nodes and 300,005 clustered‑index rows, then discards the first 300,000 rows, causing massive random I/O.

In contrast, the optimized query only accesses the 5 needed index leaf nodes and then fetches the corresponding 5 clustered‑index rows.

Buffer‑pool experiment:

Running the original query loaded 4,098 data pages and 208 index pages into the InnoDB buffer pool.

Running the optimized query loaded only 5 data pages and 390 index pages.

SQL to check buffer‑pool page counts:

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;

To ensure a clean buffer pool on each restart, disable the options innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup, then restart MySQL:

mysqladmin shutdown
/usr/local/bin/mysqld_safe &

After the restart, the buffer‑pool statistics are empty, confirming that the previous pages were cleared.

Conclusion: Large‑offset LIMIT queries cause unnecessary page reads, pollute the buffer pool, and dramatically degrade performance. Rewriting the query to first fetch primary‑key IDs in a subquery and then join eliminates the wasteful I/O.