Optimizing MySQL Pagination for Large Tables Using Indexes, Subqueries, and Joins

Preparation

The author first checks the total number of records in the edu_test table.

select count(id) from edu_test;

Result: 500,000 rows.

Analysis Process

Three LIMIT queries are executed to fetch 10 rows starting from different offsets, measuring execution time.

select * from edu_test limit 0, 10;

Time: 0.05 sec (10 rows).

select * from edu_test limit 200000, 10;

Time: 0.14 sec (10 rows).

select * from edu_test limit 499000, 10;

Time: 0.21 sec (10 rows).

Observation: Query time increases as the offset grows because LIMIT pagination triggers a full‑table scan.

explain select * from edu_test limit 200000, 10;

The plan shows a full scan (type=ALL) with ~499,483 rows examined.

Conclusion: LIMIT pagination scans the entire table up to the offset before returning the requested rows.

Optimization

Goal: Reduce the scanned range to locate the needed rows faster.

Solution 1 – Use an ordered unique index (primary key) to limit the scan range.

Solution 2 – Apply a subquery to locate the start row first (delayed query).

Solution 3 – Use a derived‑table join to fetch the desired rows.

Solution 1: Ordered Index Filtering

select * from edu_test where id > 499000 order by id asc limit 10;

Execution time drops to 0.14 sec. The EXPLAIN shows type=range using the PRIMARY index.

select * from edu_test where id between 499000 and 499020 order by id asc limit 10;

Further narrows the range, execution time 0.09 sec.

Solution 2: Subquery

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

Execution time: 0.16 sec. EXPLAIN reveals a range scan on the outer query and an index scan on the subquery.

Solution 3: Join Query

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

Execution time: 0.16 sec. The plan shows a derived table and an index lookup.

Actual Business Scenario

When designing primary keys, using sequential, ordered identifiers (e.g., distributed IDs with business logic) helps pagination performance because the range can be limited efficiently.

In practice, adding a prefix filter such as WHERE id LIKE '10289%' can further narrow the scan before applying LIMIT.

Key takeaway: Keep primary keys unique and ordered to avoid hotspot issues and to enable effective range‑based pagination optimizations.