MySQL Pagination and Large Dataset Handling

Why applications paginate

Pagination limits how much data a database query returns and how much an application must transfer, deserialize, and display at once. Returning every matching row can consume server resources, network bandwidth, and application memory. A page size is part of the API contract: set a reasonable maximum, validate it, and avoid allowing a caller to request an unbounded result. Pagination also needs a stable ordering so users do not see records move unpredictably between requests.

Always use ORDER BY. MySQL does not guarantee row order without it, even if a table appears to return rows by primary key in tests. When sort values can tie, add a unique tie-breaker such as the primary key. This makes the ordering deterministic for a fixed dataset and is necessary for reliable cursor construction.

Offset pagination

Offset pagination uses LIMIT with OFFSET (or MySQL's comma syntax) to skip earlier rows and return the requested page. It is straightforward and supports jumping to numbered pages. However, the database may need to find and discard many preceding rows for a deep offset, so later pages can become slower as the dataset grows. Inserts and deletes between page requests can shift positions, causing duplicates or missing records from the user's traversal.

SELECT order_id, created_at, total
FROM orders
ORDER BY created_at DESC, order_id DESC
LIMIT 25 OFFSET 500;

The unique identifier stabilizes ordering among equal timestamps, but the query may still process skipped rows. Offset is often acceptable for small tables, shallow navigation, or products that require page numbers. Measure deep-page latency with realistic data and indexes rather than assuming LIMIT makes every query cheap.

Keyset (cursor) pagination

Keyset pagination uses the values from the last row of the previous page as a cursor. For descending order by created_at and order_id, the next page asks for rows with an earlier timestamp or a smaller identifier when timestamps tie. With a matching index, the database can seek from the cursor rather than skipping a large number of rows. Keyset pagination works well for sequential browsing but does not naturally jump to an arbitrary page number.

SELECT order_id, created_at, total
FROM orders
WHERE created_at < ?
   OR (created_at = ? AND order_id < ?)
ORDER BY created_at DESC, order_id DESC
LIMIT ?;

Bind the cursor values as parameters and construct the next cursor from the last returned row. The ordering columns should be stable and preferably non-null; nullable sort values require explicit ordering rules and predicates. Add an index that supports the filter and ordering, then verify with EXPLAIN. Cursor tokens returned to clients should be validated and may be encoded or signed if tampering could alter query scope.

Consistency under concurrent changes

Even deterministic ordering does not freeze a dataset across multiple requests. New rows may appear before the cursor, and updates to ordering columns can move records. If a consistent snapshot across all pages is a hard requirement, that requires a deliberate snapshot or export workflow; holding a database transaction open across a user's browsing session is usually inappropriate. Define whether the interface is a live feed, a best-effort list, or a consistent report.

For exports or batch processing, process bounded key ranges and save progress so work can resume. Avoid loading the entire dataset into application memory. Use backpressure in streaming workflows and handle cancellation and partial output. If a user downloads a large report, generate it asynchronously where appropriate and secure the resulting file.

Page size, filters, and indexes

Validate page size and cap it server-side. Bind LIMIT values where the driver supports it; otherwise parse as a bounded integer and never append arbitrary input to SQL. User-selected sort fields cannot normally be bound as values, so select them from a fixed allowlist. Filters should be parameterized and authorized. A matching composite index can support a frequent filter and order, but index design should account for write cost and existing access paths. See index design and safe query construction.

For search results, decide how total counts are needed. COUNT(*) over a large filtered dataset can be expensive even when page retrieval is quick. Consider whether the UI truly needs an exact total, whether a cached estimate is acceptable, or whether next/previous navigation is sufficient. Do not compute expensive counts automatically for every page without measuring the impact.

Choose a strategy and verify

Use offset pagination for simpler shallow page navigation when data size permits. Prefer keyset pagination for deep sequential browsing and stable cursor-based APIs. Use a deterministic sort, bind cursor values, cap page sizes, and align indexes with common filters. Test empty pages, ties, NULL sort fields, inserted rows, deleted rows, and changed ordering values. Measure both first-page and later-page latency with production-like data.

Pagination is a data-access design that balances user navigation, consistency, and resource use. Make ordering explicit and choose offset or keyset based on product behavior and measured scale. Continue with WHERE, ORDER BY, and LIMIT, indexes, and EXPLAIN.