Why Large‑Offset LIMIT Is Slow in MySQL and How to Speed It Up

When querying a non‑sharded MySQL table of about 9.5 million rows, a plain SELECT … LIMIT 0,10 took 16 seconds, but after moving the filter into a sub‑query that first selects only the primary‑key IDs and then joins the remaining columns, the same request finished in 347 ms.

Operation and Principle

The optimization moves the filtering condition into a sub‑query that returns only the primary‑key values, then joins those IDs to the main table. This reduces the number of row‑lookups ("回表"), leveraging delayed joins or sub‑queries to handle many pagination scenarios efficiently.

SQL Before and After

-- Original SQL
SELECT <em>various fields</em>
FROM `table_name`
WHERE <em>conditions</em>
LIMIT 0,10;

-- Optimized SQL
SELECT <em>various fields</em>
FROM `table_name` main_table
RIGHT JOIN (
    SELECT <em>primary key only</em>
    FROM `table_name`
    WHERE <em>conditions</em>
    LIMIT 0,10
) temp_table ON temp_table.id = main_table.id;

Test Environment

MySQL version 5.7.17. Table test schema:

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

The table contains 5,242,882 rows.

Performance Comparison

Query with a large offset: SELECT * FROM test WHERE val=4 LIMIT 300000,5; Execution time: ~16 seconds.

Rewritten using an inner join on the IDs:

SELECT * 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 seconds, a dramatic improvement.

Why the Original Query Is Slow

MySQL first scans the index leaf nodes to locate the matching rows, then for each leaf node it fetches the full row from the clustered index. With an offset of 300,000, it must read 300,005 index entries and the same number of clustered rows, only to discard the first 300,000 rows. This results in massive random I/O.

Even if the index is used, the engine still performs 300,005 lookups on the clustered index, loading many pages that never appear in the final result set.

Buffer‑Pool Evidence

By checking INNODB_BUFFER_PAGE, the slow query loaded 4,098 data pages and 208 index pages into the buffer pool, whereas the optimized query loaded only 5 data pages and 390 index pages, confirming far fewer I/O operations.

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

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

To ensure a clean test, the buffer pool was cleared by shutting down MySQL and restarting it.

Conclusion

Using a large OFFSET with LIMIT forces MySQL to read and discard a huge number of rows, causing heavy random I/O and polluting the buffer pool. Rewriting the query to first fetch the primary‑key IDs and then join the remaining columns dramatically reduces I/O, execution time, and buffer‑pool usage.

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