Why Does Pagination Slow Down at 10 Million Records? Rethinking MySQL LIMIT in Java Backends

What LIMIT 1000000, 20 Actually Does

When a query such as SELECT * FROM orders ORDER BY id DESC LIMIT 1000000, 20; is executed, MySQL first retrieves the first 1,000,020 rows that satisfy the order, then discards the first 1,000,000 rows and returns the last 20. The offset forces the engine to process a huge amount of data even though only a few rows are returned.

This leads to the classic deep‑pagination symptom:

Page 1:   20 ms
Page 100: 30 ms
Page 1,000: 80 ms
Page 10,000: 500 ms
Page 50,000: 3 s

The SQL itself does not change; the slowdown appears only as the data volume grows.

Why Indexes May Not Solve the Problem

Adding an index (e.g., on created_at) can avoid a full‑table sort, but the engine still needs to scan millions of index entries to reach the desired offset. If the query selects columns not covered by the index, MySQL must perform many “back‑table” lookups, further increasing cost.

Thus an index helps “find data faster” but does not eliminate the need to skip a large offset.

First Optimization: Delayed Join (Late Association)

Instead of fetching all columns in the first step, retrieve only the primary keys for the required page and then join back to the full rows:

SELECT o.*
FROM orders o
INNER JOIN (
  SELECT id
  FROM orders
  ORDER BY id DESC
  LIMIT 1000000, 20
) t ON o.id = t.id
ORDER BY o.id DESC;

This reduces the amount of row data scanned because the sub‑query works only with the indexed id. However, the offset is still 1,000,000, so the improvement is limited.

High‑Performance Solution: Cursor (Keyset) Pagination

When the business permits, use the last seen primary‑key value as a cursor:

-- First page
SELECT * FROM orders
WHERE id < 9223372036854775807
ORDER BY id DESC
LIMIT 20;

Assuming the last row’s id is 9823711, the next page is fetched with:

SELECT * FROM orders
WHERE id < 9823711
ORDER BY id DESC
LIMIT 20;

With an index on id, the engine can start reading directly after the previous cursor, making the cost of page 1 and page 100 000 almost identical. This is known as keyset or cursor pagination.

Designing Java Pagination APIs

Traditional offset‑based API: GET /api/orders?page=10000&pageSize=20 Cursor‑based API: GET /api/orders?cursor=9823711&size=20 The first request omits cursor and returns the latest rows together with nextCursor and hasMore. Example response:

{
  "items": [{"id":9823730,"orderNo":"NO10001"}, {"id":9823711,"orderNo":"NO10020"}],
  "nextCursor": 9823711,
  "hasMore": true
}

Backend implementation (simplified):

public CursorPage queryOrders(Long cursor, int size) {
    int limit = Math.min(size, 100);
    List<Order> orders;
    if (cursor == null) {
        orders = orderMapper.selectLatest(limit + 1);
    } else {
        orders = orderMapper.selectBefore(cursor, limit + 1);
    }
    boolean hasMore = orders.size() > limit;
    if (hasMore) {
        orders = new ArrayList<>(orders.subList(0, limit));
    }
    Long nextCursor = orders.isEmpty() ? null : orders.get(orders.size() - 1).getId();
    return new CursorPage<>(orders, nextCursor, hasMore);
}

The mapper query uses a composite index when ordering by non‑unique columns, e.g.:

SELECT id, order_no, user_id, amount, status, created_at
FROM orders
WHERE id < #{cursor}
ORDER BY id DESC
LIMIT #{size};

Common Pitfall: Filtering After Pagination

Fetching a page and then applying Java‑side filters can produce incomplete pages and inaccurate totals. All filtering, permission checks, and ordering should be pushed down to SQL before applying LIMIT.

Choosing the Right Pagination Strategy

Ask four questions:

Does the user need random page jumps? If yes, offset pagination may be acceptable; otherwise prefer cursor pagination.

How large is the data set? Below a few hundred thousand, ordinary pagination works; at tens of millions, deep‑pagination issues must be addressed.

Is the sort field stable? When sorting by non‑unique columns (e.g., created_at), add a unique column such as id to guarantee deterministic order.

Does the UI really need an exact total count? If not, return only hasMore or a cached/approximate count.

Dual‑Mode Pagination Recommendation

For mature Java systems, keep both capabilities:

Offset Pagination – suited for admin panels where users may need to jump to a specific page and the data volume is manageable.

Cursor Pagination – ideal for C‑end feeds, order histories, messages, logs, comments, and any scenario with massive, continuously scrolling data.

Do not force a single solution across all modules; choose the method that matches the business scenario.

Additional Performance Considerations

Counting total rows with SELECT COUNT(*) can become a bottleneck on tens of millions of rows, especially with complex joins. Options include caching the count, asynchronous or approximate statistics, or omitting the total altogether and using hasMore instead.

In summary, the root cause of the “LIMIT 1000000, 20” slowdown is the large offset, not a syntax error. Proper pagination design—whether delayed join, cursor pagination, or a hybrid approach—combined with stable sorting and judicious use of totals, ensures Java back‑ends remain performant as data grows to tens of millions of rows.