Why MySQL LIMIT Can Kill Performance and How to Fix It

1. MySQL Index Structure

MySQL primarily uses B+‑tree indexes, a balanced multi‑way search tree where each node contains up to m child pointers. Internal nodes store only key values and child pointers, while leaf nodes store the key and either a pointer to the data record (clustered index) or a pointer to the record’s address (non‑clustered index). Only one clustered index (usually the primary key) can exist per table, whereas multiple non‑clustered indexes are allowed.

Two common forms are clustered and non‑clustered indexes. In a clustered index the leaf nodes contain the full data rows; in a non‑clustered index the leaf nodes contain only the key and a pointer to the row.

2. MySQL Query Execution Process

Full table scan : scans every row, used when no suitable index exists.

Index scan (range scan) : uses an index to locate matching rows, suitable for simple predicates.

Covering index scan : all required columns are present in the index, eliminating the need to read the data pages.

Index lookup (back‑table lookup) : first finds matching index entries, then follows pointers to fetch the full rows.

3. LIMIT Performance Issue

The example query SELECT * FROM test WHERE val=4 LIMIT 300000,5; requests rows 300001‑300005 where val=4 in a table of 5 million rows. MySQL chooses the val index, walks the B+‑tree to the first matching leaf, then scans at least 300 005 leaf entries to reach the offset. This forces:

Scanning ≥ 300 005 index pages.

Accessing ≥ 300 005 data pages (because SELECT * needs all columns).

Sorting and filtering ≥ 300 005 rows in memory.

These steps generate massive random I/O and CPU/memory consumption, which is why LIMIT can severely degrade performance.

4. How to Optimize

Use a covering index : select only the needed columns so the query can be satisfied from the index alone.

SELECT id, val FROM test WHERE val=4 LIMIT 300000,5;

Use a sub‑query : first fetch the primary keys, then retrieve the full rows by primary‑key lookup.

SELECT * FROM test WHERE id IN (SELECT id FROM test WHERE val=4 LIMIT 300000,5);

Use partitioned tables : split a large table into smaller partitions (e.g., by val) so the query touches only the relevant partition. SELECT * FROM test_4 LIMIT 300000,5; These techniques reduce the amount of data MySQL must scan, dramatically improving query speed.