Beyond Simple LIMIT: Advanced Pagination Techniques for MySQL and Elasticsearch

Many developers assume that MySQL LIMIT offset is the only way to paginate, but three alternative methods can outperform it in specific scenarios.

Limit Offset Pagination

For a page size of 10 and page 3, the query uses LIMIT 20,10. The front end must send page size and current page, and the back end builds the SQL. This method supports page jumps and can return total record count, but suffers from slow queries on deep pages because MySQL must scan many rows, increasing CPU and memory load.

Important: Pagination must specify an ordering column; otherwise duplicate rows may appear. If no natural order exists, use the primary‑key ID.

Primary‑Key Filtering (Keyset Pagination)

Instead of offset, add a condition on the primary‑key ID:

-- Before improvement
SELECT * FROM students WHERE ... ORDER BY id DESC LIMIT 1000,20;
-- After improvement
SELECT * FROM students WHERE ... AND id < lastMinId ORDER BY id DESC LIMIT 20;

Each request passes the smallest ID from the previous page ( lastMinId), ensuring the next page only scans the newest rows, dramatically reducing the search range.

Limitations: This works only when the ordering can be based on the primary key; other sort orders require different approaches.

HasMore Scrolling Pagination

In scenarios like a mobile app’s purchase history where total count isn’t needed, request one extra record (pageSize+1). If the extra record exists, set hasMore=true and continue; otherwise stop. This eliminates the costly SELECT COUNT(*) query.

Elasticsearch Pagination

Elasticsearch supports similar pagination with from and size parameters:

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

Deep pagination also strains ES; the default max_result_window is 10,000, which can be increased for low‑frequency B‑side queries.

Choosing the Right Method

No single pagination technique is universally best. Use simple LIMIT/OFFSET for small, shallow pages; keyset pagination for deep navigation with a stable primary‑key order; has‑more scrolling for infinite‑scroll UI where total count isn’t required; and Elasticsearch pagination when working with full‑text search indices, adjusting max_result_window as needed.