Practical SQL Guide · MySQL 8.0

Why deep OFFSET pagination gets slower

Compare OFFSET and keyset pagination by scan work, stable ordering, and behavior under data changes.

Problem and reasoning

LIMIT with OFFSET fits smaller data sets and admin screens that must jump to a numbered page. It is easy to implement and explains page numbers directly, but deeper pages usually require the database to locate and discard progressively more preceding rows.

Cursor pagination, also called keyset pagination, stores the last row's sort key instead of a page number. The next query continues after that key. With ascending primary-key order, id > last_id lets an index seek to the next starting position.

When the sort value is not unique, the cursor must contain the complete ordering tuple. ORDER BY created_at DESC, id DESC requires a next-page predicate that compares both values; remembering only the timestamp can repeat or omit rows that share it.

Concurrent changes also affect the choice. OFFSET reads by position, so earlier inserts or deletes shift later pages. A cursor continues around the last key already seen and usually suits feeds and sequential browsing, but it does not naturally jump to page 500.

Before release, compare EXPLAIN ANALYZE with realistic indexes and target page depths. Specify whether the interface needs a total page count, arbitrary jumps, reverse navigation, or changeable sorting; a four-row example cannot establish production timing.

Examples

Sample table

Table：orders

orders
id	customer_id	product_id	name	department	amount	status	created_at	discount
1	101	201	Alice	Sales	1200	paid	2026-01-03	0
2	102	202	Bob	IT	900	pending	2026-01-08	50
3	101	201	Cindy	Sales	1500	paid	2026-02-02	100
4	104	203	David	HR	700	cancelled	2026-02-15	0

Before / Not recommended

SELECT * FROM orders ORDER BY id LIMIT 2 OFFSET 2;

Before result

result
id	customer_id	product_id	name	department	amount	status	created_at	discount
3	101	201	Cindy	Sales	1500	paid	2026-02-02	100
4	104	203	David	HR	700	cancelled	2026-02-15	0

After / Recommended

SELECT * FROM orders WHERE id > 2 ORDER BY id LIMIT 2;

After result

result
id	customer_id	product_id	name	department	amount	status	created_at	discount
3	101	201	Cindy	Sales	1500	paid	2026-02-02	100
4	104	203	David	HR	700	cancelled	2026-02-15	0

Why it is better：The sample rows match, but the mechanisms differ: OFFSET skips two rows, while the cursor starts after known primary key 2. The gap grows with deeper offsets.