Optimizing MySQL LIMIT Queries by Reducing Unnecessary Row Scans

In a table with 9,555,695 rows, a query using SELECT * FROM test WHERE val=4 LIMIT 300000,5 took 16 s 938 ms because MySQL had to scan 300,005 index entries and corresponding clustered rows before discarding the first 300,000 rows.

The author proposes moving the LIMIT into a sub‑query that retrieves only the primary‑key IDs, then joining back to the main table, which reduces the execution time to 347 ms (execution 163 ms, fetching 184 ms).

Original SQL (slow):

-- 优化前SQL
SELECT  various_fields
FROM `table_name`
WHERE  various_conditions
LIMIT 0,10;

Optimized SQL (fast):

-- 优化后SQL
SELECT  various_fields
FROM `table_name` main_table
RIGHT JOIN (
  SELECT  id  -- sub‑query only selects primary key
  FROM `table_name`
  WHERE  various_conditions
  LIMIT 0,10
) temp_table ON temp_table.id = main_table.id;

The performance gain comes from eliminating the costly back‑table lookups: the original query reads the index leaf nodes, then for each matching row fetches the full row from the clustered index, resulting in massive random I/O. The rewritten query fetches only the needed primary keys, then retrieves the corresponding rows in a single join, dramatically reducing I/O.

To verify the hypothesis, the author examined InnoDB buffer‑pool statistics. The slow query loaded 4,098 data pages and 208 index pages, while the optimized join loaded only 5 data pages and 390 index pages, confirming far fewer page reads.

Additional steps include clearing the buffer pool on MySQL restart (disabling innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup) to ensure accurate measurements.

References: 1. https://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/ 2. https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-buffer-pool-tables.html