Why MySQL Pagination Slows Down and How to Speed It Up

Background

During a recent interview we discussed pagination performance problems, which are common when dealing with large data sets.

Analysis

Using LIMIT together with OFFSET forces MySQL to scan and discard a growing number of rows as the offset increases, leading to slower queries. For example,

SELECT * FROM t_name WHERE c_name1='xxx' ORDER BY c_name2 LIMIT 2000000,25;

requires scanning over two million rows before returning 25 rows.

As described in High Performance MySQL , large offsets cause the engine to read many unnecessary rows and then discard them.

Optimization Strategies

Use indexed subqueries : Ensure columns used for sorting and filtering are indexed, then locate the starting id in the index tree and fetch rows from that point.

Adopt cursor‑ or key‑based pagination : Remember the primary key of the last row on the previous page and query the next page using a condition such as a.id > last_id instead of using OFFSET.

Reduce the result set : Select only the needed columns instead of SELECT * to lower the amount of data transferred.

Partition large tables : Use table partitioning so queries operate on smaller data subsets.

Cache frequent queries : Store results in Redis, Memcached, or similar caches to avoid repeated database hits.

Consider physical design : Faster storage (SSD) and sufficient memory also improve query performance.

Conclusion

Applying the above techniques can dramatically improve pagination performance in MySQL, especially for massive tables; the best approach depends on the specific workload and environment.