Analyzing and Optimizing MySQL Pagination Performance with Large Offsets

Background : After a production incident where a client repeatedly called an API with a massive offset (e.g., offset=1800000&limit=500), the MySQL cluster became sluggish because each request forced the server to scan and discard millions of rows before returning the requested page.

Problem Analysis : The original query

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

requires MySQL to read 2,000,025 rows, discarding the first 2,000,000, which is inefficient for large offsets. This behavior is explained in High Performance MySQL Chapter 6.

Data Simulation : To reproduce the issue, two tables ( dep and emp) were created and populated with 5 000 000 employee rows and 120 department rows using stored procedures and helper functions ( rand_string, rand_num). Example table creation:

DROP TABLE IF EXISTS dep; CREATE TABLE dep (id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, depno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, depname VARCHAR(20) NOT NULL DEFAULT "", memo VARCHAR(200) NOT NULL DEFAULT "");

and employee insertion procedure insert_emp that inserts 5 000 000 rows in a single transaction.

Testing : Two queries were run:

Offset = 100, limit = 25 – executed in 0.001 s.

Offset = 4 800 000, limit = 25 – executed in 12.275 s.

The huge difference confirms the offset problem.

Solution 1 – Index‑Covering Subquery : Use the primary‑key index to locate the start row, then fetch the page:

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 4800000,1) ORDER BY a.id LIMIT 25;

This reduces execution time to ~1.5 s for the large offset.

Solution 2 – Remember Last Position : Store the last returned id (e.g., 100 or 4 800 000) and query with WHERE id > last_id LIMIT 25. This yields constant‑time pagination (≈0.001 s) but only works for sequential scrolling.

Solution 3 – Degradation Strategy : Impose a maximum allowed offset (e.g., 10 000). Requests exceeding this return a 4xx error or empty result, forcing users to narrow their search criteria.

Conclusion : Large LIMIT offsets cause full‑table scans and severe latency. Combining index‑covering subqueries, cursor‑style pagination, and offset throttling provides fast, predictable performance while protecting the database from abusive paging patterns.