Why MySQL Pagination Slows Down at High Offsets and How to Fix It

Background

One night a teammate received a call that a data‑query API was being hammered, causing the MySQL cluster to slow down. The slow‑query log showed a request with an offset of 1,800,000 and limit 500, meaning the client was trying to read page 3,601 of a dataset that contains over 100 million rows.

Analysis

The query itself is well‑indexed, but using LIMIT offset, count with a huge offset forces MySQL to scan and discard millions of rows before returning the requested 25 rows, which dramatically increases response time.

Data Simulation

To reproduce the problem, two tables (employee and department) and helper functions for random strings and numbers were created, followed by stored procedures that insert 5 million employee rows and 120 department rows.

/* Department table */
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 ""
);

/* Employee table */
drop table if exists emp;
create table emp(
  id int unsigned primary key auto_increment,
  empno mediumint unsigned not null default 0,
  empname varchar(20) not null default "",
  job varchar(9) not null default "",
  mgr mediumint unsigned not null default 0,
  hiredate datetime not null,
  sal decimal(7,2) not null,
  comn decimal(7,2) not null,
  depno mediumint unsigned not null default 0
);

/* Random string function */
DELIMITER $
DROP FUNCTION IF EXISTS rand_string;
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
  DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
  DECLARE return_str VARCHAR(255) DEFAULT '';
  DECLARE i INT DEFAULT 0;
  WHILE i < n DO
    SET return_str = CONCAT(return_str, SUBSTRING(chars_str, FLOOR(1+RAND()*52), 1));
    SET i = i + 1;
  END WHILE;
  RETURN return_str;
END $
DELIMITER ;

/* Insert employee data */
DELIMITER $
DROP PROCEDURE IF EXISTS insert_emp;
CREATE PROCEDURE insert_emp(IN START INT, IN max_num INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  SET autocommit = 0;
  REPEAT
    SET i = i + 1;
    INSERT INTO emp(empno, empname, job, mgr, hiredate, sal, comn, depno)
    VALUES (START+i, rand_string(6), 'SALEMAN', 0001, NOW(), 2000, 400, rand_num());
  UNTIL i = max_num END REPEAT;
  COMMIT;
END $
DELIMITER ;
CALL insert_emp(0, 5000000);

Test

Two queries were executed: one with a small offset (100) and another with a huge offset (4,800,000). The small‑offset query returned instantly, while the large‑offset query took over 12 seconds.

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
ORDER BY a.id DESC
LIMIT 100,25;  -- 0.001s

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
ORDER BY a.id DESC
LIMIT 4800000,25;  -- 12.275s

Solution

1. Index covering + subquery

First fetch the primary‑key id at the desired offset using a subquery, then retrieve the next 25 rows based on that id. This lets MySQL use the index to jump directly to the start position.

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;

Execution time dropped to 0.106 s for the 4.8 million‑offset case.

-- Result for offset 100
Time: 0.106s

-- Result for offset 4,800,000
Time: 1.541s

2. Redefine start position

Remember the last id of the previous page and use it as the lower bound for the next query, eliminating the need for a large OFFSET.

SELECT a.id,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 > 100
ORDER BY a.id
LIMIT 25;

This approach consistently returns results in ~0.001 s because only 25 rows are scanned.

3. Degradation strategy

Set a maximum allowed OFFSET; if a request exceeds this threshold, return an empty result or a 4xx error to prevent abusive full‑table scans.

Conclusion

Large OFFSET values cause MySQL to scan and discard massive amounts of data, leading to severe latency. Using index‑covering subqueries, remembering the last primary‑key, or limiting the OFFSET size can dramatically improve pagination performance. The article also demonstrates how to generate massive test data and validates each optimization with concrete timing results.