Why LIMIT Can Slow Down MySQL Queries and How to Fix It

Background

A table with about 9.5 million rows was queried using pagination with LIMIT. The original query took 16.938 seconds (execution 16.831 s, fetching 107 ms). After applying a rewrite, the same operation completed in 347 ms (execution 163 ms, fetching 184 ms).

Optimization Technique

Operation: Move the filtering condition into a subquery that only selects the primary‑key ID, then join the subquery result with the main table to retrieve the remaining columns. Principle: Reduce the number of “back‑table” lookups (random I/O) by avoiding full row scans for rows that will be discarded.

SQL Before and After

-- Original SQL (slow)
SELECT *
FROM `table_name`
WHERE <em>various conditions</em>
LIMIT 0,10;

-- Optimized SQL (fast)
SELECT *
FROM `table_name` main_table
RIGHT JOIN (
    SELECT <em>primary_key</em>
    FROM `table_name`
    WHERE <em>various conditions</em>
    LIMIT 0,10
) temp_table ON temp_table.<em>primary_key</em> = main_table.<em>primary_key</em>;

Why LIMIT with Large Offsets Is Slow

When LIMIT offset, count uses a large offset, MySQL must scan the index leaf nodes up to offset + count entries, then for each leaf node fetch the full row from the clustered index. For example, SELECT * FROM test WHERE val=4 LIMIT 300000,5 forces MySQL to read 300 005 index entries and 300 005 clustered rows, only to discard the first 300 000 rows.

This results in massive random I/O. The diagram below illustrates scanning many index nodes and cluster‑index rows before the final five rows are returned:

Ideally, MySQL would walk the index leaf nodes directly to the last five needed rows and then fetch only those five rows from the clustered index, requiring just five random I/O operations:

Experimental Verification

To verify the hypothesis, two queries were compared by examining the number of pages loaded into InnoDB’s buffer pool:

Simple SELECT * FROM test WHERE val=4 LIMIT 300000,5 Join‑based version

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

Buffer‑pool page counts after each statement:

-- After simple LIMIT query
PRIMARY    4098 pages
val          208 pages

-- After join‑based query
PRIMARY       5 pages
val         390 pages

The join‑based query loaded only the five pages needed for the final result, confirming the excessive I/O caused by the large‑offset LIMIT query.

Clearing Buffer Pool for Tests

To ensure a clean test environment, the buffer pool was cleared by disabling innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup, then restarting MySQL:

mysqladmin shutdown
/usr/local/bin/mysqld_safe &

After the restart, the buffer‑pool page counts returned to zero before running the test queries.

Conclusion

Using LIMIT with a large offset forces MySQL to read many unnecessary rows, leading to heavy random I/O and poor performance. Rewriting the query to first select primary keys in a subquery (or using an indexed covering scan) and then joining dramatically reduces I/O and execution time.

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