Optimizing MySQL Pagination for Large Datasets

Preparation

# Total record count is 500000
mysql> select count(id) from edu_test;
+-----------+
| count(id) |
+-----------+
|    500000 |
+-----------+
1 row in set (0.05 sec)

Analysis Process

Querying the first 10 rows (offset 0):

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

Querying 10 rows starting at offset 200,000:

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

Querying 10 rows starting at offset 499,000 (near the end of the table):

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

Observation: the deeper the pagination offset, the longer the query takes.

Explain plan for the offset 200,000 query shows a full table scan:

mysql> explain select * from edu_test limit 200000, 10;
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | edu_test | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499483 | 100.00   | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set (0.09 sec)

Reasoning: LIMIT pagination forces MySQL to scan the whole table up to the offset before returning the requested rows.

Optimization Strategies

Goal: quickly locate the rows to be returned and shrink the scan range.

Strategy 1 – Use an ordered unique index to limit the scan.

Strategy 2 – Delayed query (locate then fetch).

Strategy 3 – Join‑based pagination.

Strategy 1: Using an ordered primary key

Prerequisite: the id column must be sequential.

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 narrow the range
mysql> select * from edu_test where id between 499000 and 499020 order by id asc limit 10;
10 rows in set (0.09 sec)

mysql> explain select * from edu_test where id between 499000 and 499020 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 |   21 | 100.00   | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set (0.08 sec)

Strategy 2: Subquery

mysql> SELECT * FROM edu_test WHERE id >= (
    SELECT id FROM edu_test ORDER BY id LIMIT 499000,1
) LIMIT 10;
10 rows in set (0.16 sec)

mysql> explain SELECT * FROM edu_test WHERE id >= (
    SELECT id FROM edu_test ORDER BY id LIMIT 499000,1
) LIMIT 10;
+----+-------------+----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | PRIMARY     | edu_test | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 1000   | 100.00   | Using where |
|  2 | SUBQUERY    | edu_test | NULL       | index | NULL          | PRIMARY | 4       | NULL | 499001 | 100.00   | Using index |
+----+-------------+----------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
2 rows in set (0.14 sec)

Strategy 3: Join‑based pagination

mysql> select * from edu_test s,
       (select id from edu_test order by id limit 499000,10) t
       where s.id = t.id;
10 rows in set (0.16 sec)

mysql> explain select * from edu_test s,
       (select id from edu_test order by id limit 499000,10) t
       where s.id = t.id;
+----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 499010 | 100.00   | NULL        |
|  1 | PRIMARY     | s          | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | t.id | 1      | 100.00   | NULL        |
|  2 | DERIVED     | edu_test   | NULL       | index  | NULL          | PRIMARY | 4       | NULL | 499010 | 100.00   | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
3 rows in set (0.10 sec)

Real‑World Business Scenario

When designing primary keys, many systems use ordered, possibly string‑based IDs that embed business logic.

To optimize pagination, one can first narrow the range using a pattern match such as WHERE id LIKE '10289%' before applying LIMIT.

Key takeaway: keep the primary key unique and ordered; this not only serves as a natural index but also enables efficient range scans and reduces hotspot contention.