Why LIMIT Slows MySQL Queries and How to Speed Them Up

Introduction: a financial table with 9.5 million rows originally required 16 s 938 ms using a simple LIMIT query, which was reduced to 347 ms after optimization.

Operation: Move the filter into a sub‑query that selects only primary‑key IDs, then join to fetch the remaining columns. Principle: Reduce row‑lookup (back‑table) operations.

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

-- Optimized SQL
SELECT ... FROM `table_name` main_table
RIGHT JOIN (
    SELECT primary_key FROM `table_name` WHERE ... LIMIT 0,10
) temp_table ON temp_table.id = main_table.id;

MySQL version 5.7.17; table schema: id BIGINT UNSIGNED PRIMARY KEY, val INT UNSIGNED (non‑unique index), source INT UNSIGNED.

Data: 5 242 882 rows inserted.

When the LIMIT offset is large, MySQL must scan many rows, causing heavy random I/O. SELECT * FROM test WHERE val=4 LIMIT 300000,5; This query takes about 16 seconds.

Rewritten query using a sub‑query to fetch IDs first reduces execution to 0.38 seconds:

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

The process involves scanning 300 005 index leaf nodes and the same number of clustered‑index rows, then discarding the first 300 000 rows, which explains the slowdown.

Illustrations of the original scan and the optimized access pattern:

Verification using InnoDB buffer‑pool page counts shows the original query loads thousands of pages, while the optimized query loads only a few.

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;

After clearing the buffer pool and restarting MySQL, the optimized query loads 5 data pages and 390 index pages versus 4 098 data pages and 208 index pages for the original query.

Conclusion: A large OFFSET in LIMIT forces MySQL to read many unnecessary rows, polluting the buffer pool; rewriting the query to fetch primary keys first dramatically improves performance.