Optimizing MySQL LIMIT‑OFFSET Queries by Using Subqueries to Reduce Table Scans

A financial ledger table with 9,555,695 rows originally required a query with LIMIT 0,10 that took 16 s 938 ms; after applying the optimization described below the same query finished in 347 ms.

Operation: Place the filtering condition in a subquery that returns only the primary‑key IDs, then join those IDs to the original table to retrieve the remaining columns.

Principle: Reduce the number of row‑lookups (back‑table operations).

-- Original SQL (slow)
SELECT ...
FROM `table_name`
WHERE ...
LIMIT 0,10;

-- Optimized SQL (fast)
SELECT ...
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;

The test uses MySQL 5.7.17. The table schema is:

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

When the OFFSET in LIMIT 300000,5 is large, MySQL must scan 300,005 index leaf nodes and then fetch the same number of clustered‑index rows, discarding the first 300,000 rows. This results in heavy random I/O and long execution time (≈ 16 s).

Rewriting the query as an inner join with a subquery that first selects the five needed IDs reduces the I/O dramatically:

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

This version finishes in about 0.38 s because only five index and data pages are accessed.

To verify the hypothesis, the author inspected the InnoDB buffer pool before and after each query. The first query loaded 4,098 data pages and 208 index pages, while the second query loaded only 5 data pages and 390 index pages, confirming that the original query reads a huge amount of useless data.

The article also notes that loading many low‑hotness pages pollutes the buffer pool. To guarantee a clean buffer pool on each restart, it recommends disabling innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup.

Author: Muscleape (originally published on jianshu.com). References: ExplainExtended , MySQL Documentation .