Offset/Limit vs Cursor Pagination in MySQL: When and How to Choose

Why Selecting Only Needed Data Matters

Good developers and DBAs follow the principle of retrieving only the columns required for a task. Avoid SELECT * unless every column is truly needed, as this reduces query execution time, network traffic, and memory usage.

Deterministic Ordering Is a Prerequisite for Paging

Before paging, a result set must have a deterministic order—identical ordering on every execution. Without it, MySQL may return rows in different sequences, breaking pagination.

Example table people with three rows where first_name is "Aaron":

id | first_name | last_name
---+------------+----------
1  | Aaron      | Francis
2  | Aaron      | Smith
3  | Aaron      | Jones

Query SELECT * FROM people ORDER BY first_name; can return rows in any order because first_name is not unique. Adding a unique column (e.g., id) to the ORDER BY clause guarantees deterministic ordering:

SELECT *
FROM people
ORDER BY first_name, id; -- id ensures a stable order

Offset/Limit Pagination

Offset/Limit is the most common MySQL paging method. It uses two keywords: LIMIT – number of rows to return OFFSET – number of rows to skip

Typical query (page size 10, page 2):

SELECT *
FROM people
ORDER BY first_name, id
LIMIT 10          -- page size
OFFSET 10         -- (page_number‑1) * page_size

Advantages

Simple to understand and implement.

No server‑side state; each request is independent.

Pages are directly addressable via URL parameters.

Drawbacks

Data drift : when rows are inserted or deleted between requests, later pages may miss or duplicate records.

Performance degradation : MySQL must read and discard the skipped rows, so deep offsets become increasingly slow.

For deep pagination, consider the Deferred Join technique, which first selects a subset in a sub‑query and then joins back to the main table:

SELECT *
FROM people
INNER JOIN (
  SELECT id
  FROM people
  ORDER BY first_name, id
  LIMIT 10 OFFSET 450000
) AS tmp USING (id)
ORDER BY first_name, id;

Frameworks such as Rails (FastPage) and Laravel (FastPaginate) provide helpers for this pattern.

Cursor‑Based Pagination

Cursor pagination records the last item the client saw and uses it to fetch the next page. It relies on WHERE conditions rather than OFFSET.

Simple Example (first page)

SELECT *
FROM people
ORDER BY id
LIMIT 10;

Result set includes rows with id 1‑10. The cursor returned to the client is id=10 (often base64‑encoded).

{
  "next_page": "(id=10)",
  "records": [ /* first‑page rows */ ]
}

Advanced Sorting with Cursor

When ordering by multiple columns (e.g., first_name then id), the cursor must contain both values. Example cursor: (first_name='Aaron', id=25995).

SELECT *
FROM people
WHERE (
  first_name > 'Aaron'
  OR (first_name = 'Aaron' AND id > 25995)
)
ORDER BY first_name, id
LIMIT 10;

Choosing the Right Strategy

If your application tolerates occasional missing rows and needs direct access to arbitrary page numbers, Offset/Limit may be sufficient. However, for deep pagination, large data sets, or rapidly changing data, cursor pagination offers more stable performance and avoids data‑drift issues. Use deferred‑join optimizations with Offset/Limit when you must keep that approach.