Why Large OFFSETs Slow MySQL Queries and How Subquery Optimization Fixes It

We have a financial ledger table with 9.5 million rows, using LIMIT for pagination. The original query took 16 s, while the optimized version took 347 ms.

Operation: Move the filter condition into a subquery that selects only primary‑key IDs, then join those IDs to retrieve other columns. Principle: Reduce back‑and‑forth lookups; see Alibaba Java Development Manual, chapter on MySQL indexes. Recommendation: Use delayed join or subquery to optimize many pagination scenarios. Explanation: MySQL does not skip offset rows; it reads offset+N rows then discards the first offset rows, causing heavy I/O when offset is large. Controlling total pages or rewriting SQL for large offsets is necessary.

Example before optimization: SELECT ... FROM `table_name` WHERE ... LIMIT 0,10; Example after optimization:

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;

Part 1 – Introduction

MySQL version 5.7.17. Table structure: id (bigint unsigned, primary key), val (int unsigned, indexed), source (int unsigned). Inserted ~5 million rows.

Large OFFSET queries like SELECT * FROM test WHERE val=4 LIMIT 300000,5 scan 300 005 index entries and then 300 005 clustered index rows, discarding the first 300 000 rows, which leads to ~26 s execution.

Rewriting as a join that first selects the IDs reduces the work to 5 index lookups and 5 row fetches, cutting execution time to ~0.38 s.

Part 2 – Verification

Buffer pool statistics show that the original query loads thousands of data pages, while the rewritten query loads only a handful, confirming the I/O reduction.

Experiments include clearing the buffer pool by restarting MySQL and disabling innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup to avoid pollution.

Part 3 – Configuration

To ensure the buffer pool is empty on each restart, turn off the dump and load options.