Optimizing MySQL LIMIT Pagination: Analysis and Solutions

The author, a senior architect, investigates the performance degradation of MySQL LIMIT pagination on a table named edu_test containing 500,000 rows, showing that deeper offsets significantly increase query time.

Preparation : First, the total record count is verified:

# 总记录数为500000
mysql> select count(id) from edu_test;
+-----------+
| count(id) |
+-----------+
|    500000 |
+-----------+
1 row in set (0.05 sec)

Analysis Process : Queries with different offsets reveal the phenomenon:

mysql> select * from edu_test limit 0, 10;
10 rows in set (0.05 sec)

mysql> select * from edu_test limit 200000, 10;
10 rows in set (0.14 sec)

mysql> select * from edu_test limit 499000, 10;
10 rows in set (0.21 sec)

The EXPLAIN output for a deep offset shows a full table scan ( type: ALL) with nearly 500,000 rows examined, confirming that LIMIT performs a complete scan before returning the requested slice.

Optimization Ideas :

Solution 1 – Use an ordered unique index : Assuming id is sequential, replace the offset with a range condition.

mysql> select * from edu_test where id > 499000 order by id asc limit 10;
10 rows in set (0.14 sec)

mysql> explain select * from edu_test where id > 499000 order by id asc limit 10;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | edu_test | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 1000   | 100.00   | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set (0.16 sec)

Further narrowing with a BETWEEN clause reduces rows examined to about 21.

Solution 2 – Subquery to locate the start row :

SELECT * FROM edu_test WHERE id >= (
  SELECT id FROM edu_test ORDER BY id LIMIT 499000,1)
) LIMIT 10;

EXPLAIN output shows a <code>range</code> scan on the primary key for the outer query and an <code>index</code> scan for the subquery, improving performance.

Solution 3 – Join with a derived table :

select * from edu_test s,
       (select id from edu_test order by id limit 499000,10) t
where s.id = t.id;

EXPLAIN indicates a join between the derived set of ids and the main table, avoiding a full scan.

Real Business Scenario : When designing primary keys, using ordered, sequential identifiers (or distributed IDs with ordering) enables these optimizations. If IDs are not numeric, a LIKE '10289%' pattern can also narrow the scan before pagination.

Takeaways : Keep primary keys unique and ordered; this property serves as an efficient index that reduces scan range, mitigates hotspot issues, and simplifies later performance tuning.