Optimizing MySQL LIMIT Queries by Reducing Row Lookups

1. Introduction

The article investigates the performance impact of using LIMIT offset, row_count in MySQL when the offset is large, showing a real‑world case where a query on a 9.5‑million‑row table took 16 seconds before optimization and only 347 ms after.

Problem Statement

Original query:

SELECT various_fields FROM `table_name` WHERE various_conditions LIMIT 0,10;

Execution time: 16 s 938 ms (execution 16 s 831 ms, fetching 107 ms).

Optimization Approach

The query condition is moved into a subquery that selects only the primary key, then the main query joins on that key to retrieve the remaining columns, eliminating costly row‑by‑row lookups.

Principle: reduce back‑table (row) lookups.

SELECT various_fields FROM `table_name` main_table
RIGHT JOIN (
  SELECT primary_key FROM `table_name` WHERE various_conditions LIMIT 0,10
) temp_table ON temp_table.primary_key = main_table.primary_key;

Optimized execution time: 347 ms (execution 163 ms, fetching 184 ms).

Test Environment

MySQL version 5.7.17. Table test 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       |                |
+--------+---------------------+------+-----+---------+----------------+

Data volume: 5,242,882 rows.

Demonstrating the Issue

Query with large offset: SELECT * FROM test WHERE val=4 LIMIT 300000,5; Result took ~15.98 seconds because MySQL scanned 300,005 index leaf nodes and the same number of clustered index rows, then discarded the first 300,000 rows.

Improved Query

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

This version completed in 0.38 seconds by fetching only the needed 5 rows after the subquery.

Buffer Pool Verification

Using information_schema.INNODB_BUFFER_PAGE the author measured the number of data and index pages loaded into the buffer pool for both queries. The original query loaded 4,098 data pages and 208 index pages, while the optimized query loaded only 5 data pages and 390 index pages, confirming reduced I/O.

Additional Observations

Large offsets cause buffer‑pool pollution by loading many rarely‑used pages, which can degrade overall performance. To avoid this, the author suggests disabling innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup when restarting MySQL.

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