Why Large OFFSETs Slow Down MySQL Queries and How to Fix Them

Background

On January 22, after work, a developer called about a production issue: an API was being invoked with an extremely large offset and limit, causing the MySQL cluster to slow down.

The request URL (with domain, module, and method placeholders) used offset=1800000 and limit=500 , which corresponds to page 3601, while the UI shows only 25 rows per page. Over 8,000 such calls were observed, likely from a multithreaded client named ApiAutotest generated by a QA account.

Analysis

The query itself is well‑optimized with proper indexes, but using a large OFFSET forces MySQL to scan and discard millions of rows. Example:

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

This forces the engine to read 2,000,025 rows and then return only the last 25, which is extremely inefficient. As the offset grows, the query becomes progressively slower, especially beyond millions of rows.

Data Simulation

To reproduce the issue, two tables ( emp and dep) are created. Random‑string and random‑number functions are defined, and stored procedures insert 5,000,000 employee rows and 120 department rows. Indexes on primary key and foreign key columns are added after data generation.

Test

Two queries are executed:

Offset 100, limit 25 – completes instantly.

Offset 4,800,000, limit 25 – takes about 12.275 seconds.

Solution

1. Index covering + subquery optimization

Fetch the starting id with a subquery and then retrieve the next rows, e.g.:

SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
FROM emp a LEFT JOIN dep b ON a.depno=b.depno
WHERE a.id >= (SELECT id FROM emp ORDER BY id LIMIT 100,1)
ORDER BY a.id LIMIT 25;

This reduces the execution time for the large‑offset query to roughly 0.106 seconds.

2. Redefine start position

Remember the last fetched primary‑key value and query with WHERE a.id > last_id instead of using OFFSET. This yields constant‑time performance (≈0 seconds) regardless of page number.

3. Degradation strategy

Set a maximum allowed offset; requests exceeding this threshold return empty results or a 4xx error, preventing massive scans and protecting the database.

Conclusion

Applying the first two solutions and limiting offset size dramatically improves query performance and safeguards the system from abuse. Rate‑limiting and throttling should also be considered for high‑frequency calls.