Pagination

What this page covers

Introduction

What is it? Pagination means returning a big result in small pieces (pages), for example 20 orders at a time. The question is how you ask the database for "the next 20".

Why do we need it? A Swiggy power user in Bengaluru may have 3,000 past orders. Sending all of them in one response is slow for the database, the network, and the phone. So the app loads 20, and loads more as the user scrolls. How you write that "load more" query decides whether page 150 takes 3 ms or 3 seconds.

Real-world analogy OFFSET is like reading page 500 of a book by starting at page 1 and counting pages with your finger. Keyset is like using a bookmark: you open the book exactly where you left off.

Business use case A typical "My Orders" API works fine at launch, when every user has a handful of orders. A year later it becomes one of the slowest endpoints, because bots and a few heavy users scroll far back and each request makes the database read and throw away lakhs of rows. Keyset pagination fixes this kind of endpoint: the deep pages become as fast as the first one, usually a few milliseconds.

Dataset Used

CREATE TABLE orders (
  order_id    BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id     BIGINT NOT NULL,
  restaurant  VARCHAR(100) NOT NULL,
  amount      DECIMAL(10,2) NOT NULL,
  status      VARCHAR(20) NOT NULL,
  created_at  DATETIME NOT NULL,
  KEY idx_orders_user_created (user_id, created_at, order_id)
) ENGINE=InnoDB;

INSERT INTO orders (user_id, restaurant, amount, status, created_at) VALUES
  (42, 'Meghana Foods',      612.00, 'DELIVERED', '2026-10-08 21:14:03'),
  (42, 'Truffles',           489.00, 'DELIVERED', '2026-10-08 13:02:47'),
  (42, 'Empire Restaurant',  356.00, 'CANCELLED', '2026-10-08 13:02:47'),
  (42, 'Third Wave Coffee',  290.00, 'DELIVERED', '2026-10-07 09:40:12'),
  (42, 'Rameshwaram Cafe',   180.00, 'DELIVERED', '2026-10-06 08:15:55');

orders (user 42, newest first)

Orders 2 and 3 have the same created_at. That is on purpose. Ties are where pagination bugs hide.

For the timing numbers below, imagine the full table has 5 crore rows and user 42 has 3,000 orders, while an admin "all orders" screen lists everything.

Beginner Core Path: Paginate These Five Rows

Pagination needs a total order: every row must have one unambiguous position. created_at alone is not enough because orders 2 and 3 tie. The unique order_id is the final tie-breaker:

ORDER BY created_at DESC, order_id DESC

The dataset already creates the matching index (user_id, created_at, order_id). That index is not required for correctness on five rows, but it is required for efficient keyset pagination at production scale.

First run OFFSET pages

With page size 2, OFFSET = (page_number - 1) * page_size.

-- Page 1: OFFSET = (1 - 1) * 2 = 0
SELECT order_id, restaurant, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC, order_id DESC
LIMIT 2 OFFSET 0;

-- Page 2: OFFSET = (2 - 1) * 2 = 2
SELECT order_id, restaurant, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC, order_id DESC
LIMIT 2 OFFSET 2;

-- Page 3: OFFSET = (3 - 1) * 2 = 4
SELECT order_id, restaurant, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC, order_id DESC
LIMIT 2 OFFSET 4;

The five rows arrive as (1, 3), then (2, 4), then (5). OFFSET is easy and supports page numbers. Bind the page size and offset as parameters in application code, reject negative values, and cap the page size (often 20–100) so one request cannot fetch an unbounded result.

Then run keyset pages

Keyset does not count from the top. It remembers the final (created_at, order_id) pair from the previous page.

-- Page 1: no cursor
SELECT order_id, restaurant, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC, order_id DESC
LIMIT 2;

-- Page 2 in MySQL: cursor is the last row from page 1, order 3
SELECT order_id, restaurant, created_at
FROM orders
WHERE user_id = 42
  AND (created_at < '2026-10-08 13:02:47'
       OR (created_at = '2026-10-08 13:02:47' AND order_id < 3))
ORDER BY created_at DESC, order_id DESC
LIMIT 2;

-- Page 3: cursor is the last row from page 2, order 4
SELECT order_id, restaurant, created_at
FROM orders
WHERE user_id = 42
  AND (created_at < '2026-10-07 09:40:12'
       OR (created_at = '2026-10-07 09:40:12' AND order_id < 4))
ORDER BY created_at DESC, order_id DESC
LIMIT 2;

You get the same (1, 3), (2, 4), (5) sequence. The important steps are:

1. Keep the same filter and ORDER BY on every page.
2. Put every sort value, including the unique tie-breaker, in the cursor.
3. For descending order, request values < the last row; ascending order uses >.
4. Back the filter and sort with a composite index: equality filters first, then sort keys.

Keyset's stability claim assumes the sort keys do not change. It avoids position-shift duplicates or gaps caused by inserts/deletes before a stable cursor; if created_at, a score, or another sort key is edited between requests, the row can still move. Exact multi-page snapshots require a different transaction strategy, covered in Deep Dive.

The remaining sections explain deep OFFSET cost, dialect-specific predicates, API cursor encoding, previous-page navigation, and totals.

Syntax

Mental Model

Offset cost grows with the page number. Keyset cost stays the same on page 1 and page 5,000.

1. LIMIT / OFFSET Pagination

The first version everyone writes:

-- Page 1
SELECT order_id, restaurant, amount, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC, order_id DESC
LIMIT 2 OFFSET 0;

-- Page 2
SELECT order_id, restaurant, amount, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC, order_id DESC
LIMIT 2 OFFSET 2;

Page 1 returns orders 1 and 3. Page 2 returns orders 2 and 4. (Page size 2 here only to keep the tables short. Real apps use 20 to 50.)

It is simple, it works with any ORDER BY, and it lets you jump to "page 37" directly. For small tables and admin screens that is often all you need.

Notice order_id in the ORDER BY. Without it, orders 2 and 3 (same created_at) can come back in any order, and a row can show up on both pages or on neither. Always add a unique column as the last sort key.

2. Why Deep Pages Get Slow

OFFSET 100000 does not mean "jump to row 100,000". There is no way to jump. The database walks the index from the start, reads 100,000 rows, throws them away, and then reads the 20 you asked for.

It is worse than it sounds. If the index does not cover all selected columns, the database fetches the full row for each of those 100,000 skipped entries before throwing them away. In InnoDB that is one extra primary key lookup per skipped row. PostgreSQL pays a similar price reading rows from the table.

Rough numbers for an admin "all orders" screen on a 5 crore row table, sorted by created_at with a good index, warm cache:

The exact numbers depend on hardware and row size. The shape does not: time grows in a straight line with the offset. Crawlers and scrapers make it worse, because they walk every page in order. One bot going from page 1 to 50,000 can keep a database core busy for hours, and the deep requests start hitting the API timeout long before it reaches the end.

You can see this yourself with EXPLAIN ANALYZE: the "rows" read on the index step will be offset + limit, not limit. See Performance and Debugging for how to read the plan.

A patch, not a fix: the deferred join. Find only the ids first using the narrow index, then fetch full rows for just those 20.

SELECT o.order_id, o.restaurant, o.amount, o.created_at
FROM orders o
JOIN (
  SELECT order_id
  FROM orders
  WHERE user_id = 42
  ORDER BY created_at DESC, order_id DESC
  LIMIT 20 OFFSET 2000
) page USING (order_id)
ORDER BY o.created_at DESC, o.order_id DESC;

The inner query still skips 2,000 entries, but it reads them from the index only (no full row lookups). This may give a substantial speedup in MySQL when avoided full-row lookups dominate, but the factor depends on row width, cache state, storage, and the execution plan. It does not change the straight-line growth.

3. Duplicates and Missed Rows

Offset counts positions, not rows. If rows are added or removed between two page loads, the positions shift.

• Insert at the top (new order, new post): the last item of page 1 shows again on page 2. Users see duplicates in an infinite feed.
• Delete above the current page (order hidden, post removed): an item slides up from page 2 into page 1's range, and the user never sees it.

For a feed this is a real bug. For an admin table that a human reloads, it usually does not matter.

4. Keyset Pagination

Instead of "skip N rows", say "give me the rows that come after the last one I saw". The last row's sort values become a bookmark, called the cursor. (This is an API cursor, a small value the client sends back. It has nothing to do with database cursors in stored procedures.)

The query below uses a row comparison, (created_at, order_id) < (x, y), which is the cleanest way to write it. It runs on both databases, but read the MySQL note after the results before you use it there.

-- Page 1: no cursor yet
SELECT order_id, restaurant, amount, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC, order_id DESC
LIMIT 2;
-- returns order 1 (21:14:03) and order 3 (13:02:47)
-- the last row is (created_at = '2026-10-08 13:02:47', order_id = 3)

-- Page 2: continue after the last row of page 1
SELECT order_id, restaurant, amount, created_at
FROM orders
WHERE user_id = 42
  AND (created_at, order_id) < ('2026-10-08 13:02:47', 3)
ORDER BY created_at DESC, order_id DESC
LIMIT 2;

Order 2 has the same timestamp as order 3, but (13:02:47, 2) < (13:02:47, 3) is true, so it comes next. Nothing is skipped and nothing repeats. That is why the cursor needs both columns.

The row comparison (a, b) < (x, y) means a < x OR (a = x AND b < y).

PostgreSQL turns the row form into a single index seek. EXPLAIN shows it as an Index Cond on ROW(created_at, order_id) < ROW(...), and the database starts reading right at the cursor.

MySQL gives the correct answer for the row form, but its optimizer does not use a row comparison with < or > to seek in the index. It uses only the user_id = 42 part, walks user 42's entries from the newest, and filters out everything newer than the cursor. For a user with 3,000 orders, the last page reads almost all 3,000. That is the same straight-line growth you were trying to escape. In MySQL, write the long form, which the range optimizer does understand:

SELECT order_id, restaurant, amount, created_at
FROM orders
WHERE user_id = 42
  AND (created_at < '2026-10-08 13:02:47'
       OR (created_at = '2026-10-08 13:02:47' AND order_id < 3))
ORDER BY created_at DESC, order_id DESC
LIMIT 2;

Either way, run EXPLAIN once on a deep page. You want to see a range scan on idx_orders_user_created with no filesort.

Rules for keyset:

• The sort must end with a unique column (order_id), so the order is total.
• The WHERE comparison must match the ORDER BY direction. DESC uses <, ASC uses >.
• Mixed directions (created_at DESC, order_id ASC) do not work with a single row comparison. You can still write the long form by hand (created_at < x OR (created_at = x AND order_id > y)), but it is easier to keep the directions the same.
• New inserts at the top do not cause duplicates, because you are always asking for rows "older than" a fixed point.

Going back a page. Flip the comparison and the sort, then reverse the rows in the app (in MySQL, use the long form with >):

SELECT order_id, restaurant, amount, created_at
FROM orders
WHERE user_id = 42
  AND (created_at, order_id) > ('2026-10-08 13:02:47', 2)   -- first row of current page
ORDER BY created_at ASC, order_id ASC
LIMIT 2;
-- then reverse the 2 rows before returning them

5. The Index Keyset Needs

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at, order_id);

Order matters: equality columns first (user_id), then the sort columns in sort order (created_at, order_id). With this index the database seeks to the cursor position inside user 42's part of the index and reads exactly 20 entries.

• InnoDB secondary indexes already store the primary key at the end, so (user_id, created_at) would also work. Writing order_id out makes the intent clear, and PostgreSQL needs it, because its indexes do not quietly include the primary key.
• MySQL 8 can scan an ascending index backwards for DESC, so you do not need a DESC index for a simple descending sort.
• If you also filter on status, either add it to the index after user_id (if it is always an equality filter) or accept that some rows get filtered after the index read.

6. Encoding a Cursor for an API

Do not send raw created_at and order_id as separate query parameters. Wrap them into one opaque string.

cursor JSON:     {"t":"2026-10-08T13:02:47","id":3}
base64url:       eyJ0IjoiMjAyNi0xMC0wOFQxMzowMjo0NyIsImlkIjozfQ

API shape:

GET /v1/users/42/orders?limit=20
GET /v1/users/42/orders?limit=20&cursor=eyJ0IjoiMjAyNi0xMC0wOFQxMzowMjo0NyIsImlkIjozfQ

Response:
{
  "items": [ ...20 orders... ],
  "next_cursor": "eyJ0Ijo...",
  "has_more": true
}

Practical tips:

• has_more for free: ask the database for LIMIT 21. If 21 rows come back, there is a next page. Return only 20.
• Opaque on purpose: clients must not build cursors themselves. This lets you change the sort columns later without breaking apps.
• Sign it if it matters: add an HMAC so users cannot edit the cursor to read other people's data. The WHERE user_id = ? from the auth token is still your real protection.
• Store timestamps in UTC and keep full precision (microseconds if the column has them). A cursor that rounds 13:02:47.512 to 13:02:47 will skip or repeat rows.
• Include the filters (status, date range) in the cursor, or reject a cursor used with different filters.

7. Total Counts

"Showing 1 to 20 of 3,48,912 orders" looks nice, but COUNT(*) is not free. InnoDB does not store a row count. It has to scan an index for every matching row. On a big filtered set that can cost more than the page query itself.

My default for anything user-facing is no total, or a capped one. Product teams often ask for the exact number, but when you show them what it costs on a big table, most settle for "1000+". Search engines show "About 1,23,00,000 results" for the same reason.

8. Offset vs Keyset

Keyset also wins for batch jobs. A script that walks 5 crore rows to backfill a column should use WHERE order_id > ? ORDER BY order_id LIMIT 1000, never OFFSET.

9. Real Cases

9.1 Swiggy or Instagram style feed

Order history, a notifications list, a feed of posts. Users scroll, new items keep arriving at the top, and nobody asks for "page 73". This is the textbook case for keyset:

• Index (user_id, created_at, order_id).
• LIMIT 21 with a cursor, return 20 plus next_cursor.
• No total count, just "You have reached the end" when has_more is false.

9.2 Admin table with page numbers

An ops dashboard in Gurugram lists today's orders for one city, with page numbers and a "Go to page" box. Filters bring it down to maybe 40,000 rows, and people rarely go past page 10. OFFSET is fine here:

• Keep a unique tiebreaker in ORDER BY.
• Cap the maximum page (for example page 500) and ask users to add filters for anything deeper.
• Use a capped count ("1000+ results") or a cheap estimate instead of an exact COUNT(*) on the whole table.
• Use the deferred join if deep pages are still slow.

Deep Dive

Why OFFSET cannot be optimized away. A B-tree index knows the order of keys, but not "the 100,000th key" without counting. Some databases store subtree counts to make that possible, but MySQL and PostgreSQL do not. So the only way to find position N is to walk N entries.

Keyset and sorting by non-unique, nullable columns. If you sort by a column that can be NULL (say delivered_at), row comparisons with NULL return unknown and rows vanish. Sort by a NOT NULL column, or use COALESCE in both the ORDER BY and the cursor, with an index on that expression.

Sorting by a score that changes (trending, "most liked") is hard for any pagination. The score changes between page loads. Feeds handle this by taking a snapshot of ranked ids (in Redis, for example) when the user opens the feed, and paging through that list.

Stable reads across pages. Even keyset reads each page in a separate transaction. If an order's created_at is edited between pages, it can move. That is usually fine. If you need a perfect snapshot (an export to a CSV for finance), run the export in one REPEATABLE READ transaction, see Isolation Levels and Locking. Run that on a replica if you can. A transaction that stays open for an hour stops the primary from cleaning up old row versions while it runs.

Real-world Use Cases

• Mobile feeds and order history: keyset with an opaque cursor.
• Public APIs (Stripe, Slack, and GitHub's GraphQL API use cursor-based pagination): next_cursor and has_more.
• Batch jobs and backfills: walk the primary key with WHERE id > ? LIMIT 1000.
• Admin panels and back-office tools: offset with page numbers, capped depth, and a capped count.
• Search results: offset for the first few pages, with a hard limit on how deep users can go.

Review Tips

• Start with the problem: "OFFSET reads and discards N rows, so it gets slower with every page."
• Mention the duplicate and missed-row bug when data changes. Many candidates only talk about speed.
• Write the keyset query on the board with a tiebreaker column and the matching composite index.
• Admit the trade-off: keyset cannot jump to page 37. Pick the tool by the screen.
• In an interview the row comparison (created_at, id) < (?, ?) is fine on the whiteboard. In a real MySQL system, ship the long form and check EXPLAIN on a deep page, because the short form quietly falls back to filtering.

Common Mistakes to Avoid

Mistake 1: Sorting by a non-unique column only

ORDER BY created_at DESC with ties gives random order between pages. Add id as the last sort key.

Mistake 2: Cursor on created_at alone

WHERE created_at < ? skips every other row that shares the last timestamp. Use the pair (created_at, id).

Mistake 3: Mismatched direction

ORDER BY ... DESC with WHERE (...) > (?, ?) returns the wrong page. DESC goes with <.

Mistake 4: No index matching the filter and sort

Keyset without (user_id, created_at, id) still sorts the whole result on every request. Check with EXPLAIN.

Mistake 5: Running an exact COUNT(*) on every page load

On a large table that count can cost more than the page. Use a capped count, an estimate, or no total.

Mistake 6: Using OFFSET in batch scripts

Page 40,000 of a backfill takes seconds per batch. Walk the primary key instead.

Mistake 7: Losing timestamp precision in the cursor

Rounding microseconds away creates duplicates and gaps. Encode the exact value.

Interview Questions

"Why does OFFSET pagination get slow on deep pages?"

The database cannot jump to row N. It walks the index from the start, reads OFFSET plus LIMIT rows, and throws away the first OFFSET of them. The amount of index work therefore grows roughly with the page number; elapsed latency also depends on caching, row width, the plan, hardware, and concurrent load.

"What is keyset pagination?"

Instead of skipping rows, you remember the sort values of the last row you returned and ask for rows after that point, like WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20. With a matching index it reads only 20 rows on every page. In MySQL I would write the comparison in its expanded OR form, because MySQL does not use a row comparison to seek in the index.

"Why do you need the id column in the cursor?"

Because created_at is not unique. Without a unique tiebreaker, rows that share the last timestamp are either skipped or repeated across pages.

"What problem does OFFSET have when new rows are inserted while the user scrolls?"

Every row shifts down by one position, so the last item of the previous page appears again on the next page. Deletes do the opposite and make rows disappear.

"What index would you create for a user's order history sorted by newest first?"

A composite index on (user_id, created_at, order_id): the equality column first, then the sort columns in the same order as the ORDER BY.

"How do you show a total count cheaply?"

Often you skip it and show only next or load more. Otherwise use a capped count like "1000+", a table statistics estimate, or a maintained counter, rather than an exact COUNT(*) on every request.

"When is OFFSET still the right choice?"

For admin tables with page numbers, small or well-filtered result sets, and when users need to jump to a specific page. Cap the maximum page and keep a unique tiebreaker in the ORDER BY.

Key Takeaways

• LIMIT/OFFSET is simple and supports page numbers, but deep pages get slower in a straight line because skipped rows are still read.
• Offset pagination shows duplicates or misses rows when data changes between page loads.
• Keyset pagination uses the last row's sort values as a cursor: WHERE (created_at, id) < (?, ?), same speed on every page. In MySQL, write it as created_at < ? OR (created_at = ? AND id < ?) so the index is used.
• Always end the sort with a unique column, and match the comparison direction to the ORDER BY.
• Keyset needs a composite index: equality filters first, then the sort columns.
• Send the cursor as an opaque base64 string, fetch LIMIT + 1 to know has_more.
• Exact totals are expensive. Prefer no total, a capped count, or an estimate.
• Use keyset for feeds, APIs, and batch jobs. Use offset for small admin tables with page numbers.