Beyond Simple LIMIT: Advanced Pagination Strategies for MySQL and Elasticsearch

Limit‑Offset Pagination

MySQL’s classic LIMIT offset, count can handle simple paging, e.g., LIMIT 20,10 for the third page of ten rows. It supports page‑jump navigation and can return the total record count so users know how many pages exist.

Supports direct page jumps.

Can return total number of records.

However, deep pagination forces MySQL to scan many rows before reaching the requested offset, causing slow queries, high CPU and memory usage, and potentially overwhelming the database under high QPS.

Pagination must always specify an ORDER BY clause; without a deterministic order, results may repeat or skip rows. If no suitable column exists, the primary‑key ID can be used as a fallback.

Primary‑Key Based Pagination (Keyset Pagination)

Instead of offset, add a condition on the primary‑key (or any column used for ordering) to fetch the next page. The previous page’s smallest ID (or largest, depending on order) is passed as lastMinId to the next query.

select * from students where xxxx查询条件xxx order by id desc limit 1000,20;

select * from students where xxxx查询条件xxx AND id < lastMinId order by id desc limit 20;

This approach narrows the search range, eliminates the need for large offsets, and dramatically reduces the amount of data MySQL must scan. It works only when the result set can be ordered by the primary‑key; other ordering strategies require different solutions.

Has‑More (Scroll) Pagination

For UI scenarios like infinite‑scroll lists where the total count is irrelevant, request pageSize + 1 rows each time. If the extra row is returned, set hasMore = true and continue; otherwise, hasMore = false. This avoids the extra SELECT COUNT(*) query.

Elasticsearch Pagination

Elasticsearch supports similar offset‑based paging and also requires a deterministic sort order. Deep pagination suffers the same performance penalty; the default max_result_window is 10,000, which can be increased for low‑frequency B‑side queries.

SearchRequest searchRequest = new SearchRequest(index);
SearchSourceBuilder sourceBuilder = new SearchSourceBuilder();
int from = (pageNum - 1) * pageSize; // start index, zero‑based
sourceBuilder.from(from);
sourceBuilder.size(pageSize);

Just like MySQL, avoid deep offsets when possible and prefer keyset‑style queries or scroll APIs for large result sets.

Choosing the Right Method

No single pagination technique is universally best. Use simple LIMIT‑OFFSET for small, user‑driven page jumps; primary‑key filtering for deep paging with high performance; has‑more scrolling for infinite‑scroll UI; and Elasticsearch pagination when searching across indexed documents, adjusting max_result_window as needed.