Why Classic MySQL Pagination “Optimization” Often Fails and When It Actually Helps

When paging through large MySQL tables, queries that retrieve rows farther from the start (higher OFFSET) tend to become slower. A widely‑circulated “optimization” rewrites the query to first select the IDs in the desired range and then join back to the table, hoping to reduce the scanned rows.

Test Environment

The author built a simple test table (InnoDB, 5 million rows) on a minimal CentOS 7 VM running MySQL 5.7. Data were inserted via a stored procedure, and the log flush mode was set to innodb_flush_log_at_trx_commit = 2 to avoid excessive write latency.

Classic Pagination "Optimization"

The typical rewrite looks like:

SELECT * FROM t INNER JOIN (SELECT id FROM t ORDER BY id LIMIT m,n) t1 ON t1.id = t.id;

If a WHERE clause is present, the rewrite becomes:

SELECT * FROM t INNER JOIN (SELECT id FROM t WHERE ... ORDER BY id LIMIT m,n) t1 ON t1.id = t.id;

Results with a Clustered Index (Primary Key)

Both the plain query and the rewritten version were executed on the primary‑key‑ordered table. The plain query for rows 4 900 000‑4 900 020 took about 8.3 seconds, while the rewritten version took 8.4 seconds – essentially no improvement and even a slight slowdown.

Results with a Non‑Clustered Index

A new column id_2 was added, populated with the same values as the primary key, and a unique index was created on it. When ordering by id_2:

Plain query: ~60 seconds.

Rewritten query: ~1.7 seconds (≈40× faster).

The execution plans show that the plain query performs a full table scan followed by a filesort, while the rewritten query scans the id_2 index, fetches only the matching primary keys, and avoids the costly sort.

Reverse‑Scan Trick for Large Offsets

When the desired rows are near the end of the index, scanning the index backwards (ORDER BY id DESC) and then re‑ordering the result set can reduce the runtime dramatically. In the author's test, the reverse‑scan version completed in 0.07 seconds compared with >8 seconds for the forward scans.

SELECT * FROM ( SELECT * FROM test_table1 ORDER BY id DESC LIMIT 99980,20 ) t ORDER BY id;

Impact of Filtering Conditions

If a WHERE clause already reduces the result set to a small number of rows, rewriting offers little benefit. Conversely, if the filter still leaves a large set, the situation reverts to the no‑filter case, and the same index‑ordering considerations apply.

Key Takeaways

When the ORDER BY column is the clustered primary key, the classic rewrite provides no performance gain.

When ordering by a non‑clustered index, the rewrite can dramatically improve speed by avoiding full scans and filesorts.

For very large offsets, scanning the index in reverse order and then re‑ordering the result is often the fastest approach.

Always analyze the specific schema, index types, and data distribution; there is no one‑size‑fits‑all pagination rule.

In summary, pagination performance hinges on B‑tree index structure and scan direction. Optimizations must be tailored to the actual index layout and query pattern rather than applied blindly.