Why LIMIT Offset Slows MySQL Queries and How to Optimize Them

There is a financial transaction table with 9,555,695 rows that originally used a LIMIT offset query, taking 16 s 938 ms (execution: 16 s 831 ms, fetching: 107 ms). After applying the optimization described below, the same query runs in 347 ms (execution: 163 ms, fetching: 184 ms).

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

Principle: reduce table‑row lookups.

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

-- Optimized after SQL
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;

1. Introduction

MySQL version: 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       |                |
+--------+---------------------+------+-----+---------+----------------+

The table contains about 5 million rows. Using a large OFFSET in a LIMIT clause (e.g., SELECT * FROM test WHERE val=4 LIMIT 300000,5) forces MySQL to scan 300,005 index leaf nodes and the same number of clustered‑index rows, then discard the first 300,000 rows, resulting in heavy random I/O and long execution time (≈16 s).

Rewriting the query as an inner join with a sub‑query that first selects the required primary‑key IDs ( SELECT id FROM test WHERE val=4 LIMIT 300000,5) and then joins back to the main table reduces the number of index and data page accesses to only the five needed rows, cutting the execution time to about 0.38 s.

Images illustrating the index‑leaf‑node scan process:

2. Verification

To confirm the hypothesis, buffer‑pool statistics were examined before and after running the two queries. The first query loaded 4,098 data pages and 208 index pages into the buffer pool, while the optimized query loaded only 5 data pages and 390 index pages, matching the expected reduction in page reads.

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;

Disabling innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup ensures the buffer pool is cleared on each MySQL restart, preventing pollution from rarely accessed pages.