Four Pagination Strategies: MySQL LIMIT OFFSET, Primary‑Key Filtering, HasMore Scrolling, and Elasticsearch

Many developers assume that MySQL LIMIT OFFSET is sufficient for pagination, but three alternative approaches can perform better in specific situations.

1. LIMIT OFFSET pagination – Simple to implement and supports page jumps and total record count, but suffers from slow queries on deep pages because MySQL must scan many rows, increasing CPU and memory load.

2. LIMIT with primary‑key filtering – Improves performance by adding a condition on the last retrieved primary‑key (e.g., id < lastMinId ) to restrict the scan range. Example before and after:

select * from students where ... order by id desc limit 1000,20;

select * from students where ... AND id < lastMinId order by id desc limit 20;

This method works only when ordering by the primary key; otherwise it cannot be applied.

3. HasMore scrolling – Suitable for infinite‑scroll interfaces where total count is unnecessary. Instead of a separate SELECT COUNT(*) , request pageSize+1 rows; if an extra row is returned, set hasMore=true , otherwise hasMore=false .

4. Elasticsearch pagination – Ideal for complex, low‑latency‑tolerant queries. Use SearchSourceBuilder.from() and .size() to set offset and limit, and always specify a sort field. Elasticsearch also faces deep‑page pressure; the default max_result_window is 10,000 and can be adjusted for low‑frequency B‑side queries.

There is no universally best pagination method; choose the one that matches your data volume, query complexity, and performance requirements.