Why Does MySQL LIMIT Slow Queries? Comparing Multiple Optimization Strategies

MySQL Index Structure

MySQL uses B+‑tree indexes. Each node can contain up to m children and must have at least ⌈m/2⌉ children for non‑root nodes. All leaf nodes reside on the same level and are linked.

Internal nodes store only the indexed key values and pointers to child nodes.

Leaf nodes store the indexed key values and either the full row (clustered index) or a pointer to the row (non‑clustered index).

Each table can have one clustered index (normally the primary key) and multiple non‑clustered indexes.

MySQL Query Execution Process

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

Index range scan : walks the index to locate matching rows.

Covering index scan : returns all required columns directly from the index, avoiding row lookups.

Index lookup (row‑by‑row) : finds matching index entries, then fetches each full row (“back‑table” query).

Performance Impact of LIMIT with a Large Offset

Query: select * from test where val=4 limit 300000,5; Table test contains 5 million rows; val is a non‑clustered index. To return rows 300001–300005 MySQL must:

Scan at least 300 005 leaf nodes of the val index, causing random I/O on index pages.

For each leaf node, follow the pointer to the data page and fetch the full row because SELECT * requires all columns, resulting in ≥ 300 005 data‑page reads.

Sort and discard the first 300 000 rows, consuming CPU and memory.

Optimization Techniques

Covering Index Scan

If only a subset of columns is needed, include them in the index so the query can be satisfied without touching data pages:

select id, val from test where val=4 limit 300000,5;

This reduces I/O to index pages only.

Subquery to Fetch Primary Keys First

First retrieve the primary keys with the large offset, then fetch the full rows by primary‑key lookup:

select * from test where id in (
  select id from test where val=4 limit 300000,5
);

The inner query scans the val index once and returns five id values. The outer query performs five point lookups, dramatically cutting page accesses.

Partitioned Tables

Split a massive table into partitions based on the filtered column. For example, partition test into ten tables test_1 … test_10 by val. Querying a single partition avoids scanning unrelated data: select * from test_4 limit 300000,5; Only the relevant partition is accessed, reducing both I/O and CPU work.