How to Speed Up MySQL LIMIT Pagination from 16 s to 0.3 s with Subquery Optimization

There is a financial flow table with 9,555,695 rows that originally required a LIMIT‑based pagination query taking 16 s 938 ms (execution: 16 s 831 ms, fetching: 107 ms). After applying the SQL adjustments described below, the query time drops to 347 ms (execution: 163 ms, fetching: 184 ms).

Operation: Place the query conditions in a subquery that selects only the primary‑key IDs, then use those IDs to join and retrieve the other attribute fields.

Principle: Reduce table‑lookup operations. MySQL does not skip offset rows; it reads offset + N rows and discards the first offset rows, which becomes extremely inefficient for large offsets. The solution is to quickly locate the required ID range and then join to fetch the full rows. This approach is recommended in Alibaba's Java Development Manual (Chapter 5, MySQL database, Index conventions, Rule 7).

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

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

The MySQL version used is 5.7.17. The table structure includes an auto‑increment primary key id and a non‑unique index on val. After inserting about 5 million rows, a query such as SELECT * FROM test WHERE val=4 LIMIT 300000,5 takes around 16 seconds because MySQL must scan 300,005 index leaf nodes and the same number of clustered index rows, then discard the first 300,000 rows.

Rewriting the query as an inner join with a subquery that first selects the IDs ( SELECT id FROM test WHERE val=4 LIMIT 300000,5) reduces the execution time to 0.38 seconds, as only the five needed rows are accessed.

Images illustrate the original process of scanning many index nodes versus the optimized approach that accesses only the required nodes.

Experimental verification uses InnoDB's buffer pool page counts. Running the original query loads thousands of data pages (e.g., 4,098) and hundreds of index pages, while the optimized query loads only a handful (5 data pages, 390 index pages), confirming the reduced I/O.

To ensure a clean buffer pool on each MySQL restart, disable innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup.

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