SQL Query Optimization: Cutting a 9M‑Row Scan from 17 s to 300 ms

Problem

A financial transaction table with 9,555,695 rows (MySQL 5.7.17) used a simple SELECT … FROM `table_name` LIMIT 0,10 query. The query took 16 s 831 ms to execute because MySQL processes LIMIT offset, row_count by scanning index leaf nodes up to offset + row_count, fetching each corresponding clustered row, and discarding the first offset rows.

Original Query

SELECT <em>...</em>
FROM `table_name`
WHERE <em>conditions</em>
LIMIT 0,10;

Running a high‑offset query such as SELECT * FROM test WHERE val=4 LIMIT 300000,5 forced MySQL to read 300,005 index entries and 300,005 clustered rows, resulting in massive random I/O and a runtime of 15.98 s.

Optimized Query

SELECT <em>...</em>
FROM `table_name` main_table
RIGHT JOIN (
    SELECT id
    FROM `table_name`
    WHERE <em>conditions</em>
    LIMIT 0,10
) temp_table ON temp_table.id = main_table.id;

The rewrite moves the filter into a sub‑query that selects only the primary‑key id, then joins back to fetch the remaining columns. Execution time dropped to 163 ms (total 347 ms including fetching).

Why LIMIT Causes Slowness

MySQL does not know in advance which index entries will satisfy the offset, so it must walk the index sequentially. For each index leaf node it performs a “回表” (back‑table) lookup on the clustered index to retrieve the full row, even for rows that will later be discarded. This results in a number of index‑leaf and clustered‑row lookups equal to offset + row_count.

Experiment Setup

MySQL version: SELECT VERSION(); → 5.7.17 Table 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       |                |
+-------+---------------------+------+-----+---------+----------------+

Inserted 5,242,882 rows.

To obtain a clean buffer pool before each test, MySQL was restarted with innodb_buffer_pool_dump_at_shutdown=0 and innodb_buffer_pool_load_at_startup=0.

Verification Method

After each query, the number of data and index pages loaded into the InnoDB buffer pool was counted:

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;

Results

Original high‑offset query ( LIMIT 300000,5) loaded 4,098 data pages and 208 index pages.

Optimized sub‑query + join loaded only 5 data pages and 390 index pages.

Running the optimized query returned the same 5 rows in 0.38 s, while the original query required 15.98 s.

Conclusion

For pagination with large offsets, rewriting the query to first fetch primary‑key IDs in a sub‑query and then join back dramatically reduces the number of back‑table lookups, random I/O, execution time, and buffer‑pool pollution.