MySQL 8.x Large Pagination Guide: From OFFSET Performance Pitfalls to Production‑Ready Cursor Pagination

Problem Definition

Deep pagination such as

SELECT * FROM orders WHERE tenant_id = 1001 ORDER BY create_time DESC LIMIT 1000000, 20

looks like a simple "go to page 50000" but forces MySQL to scan, sort, back‑track and discard millions of rows before returning the last 20. This triggers CPU spikes, buffer‑pool misses, random I/O bursts, connection‑pool exhaustion and upstream timeouts, often causing a full‑system outage.

Why OFFSET Is a Performance Black‑Hole

InnoDB stores data in 16 KB pages; the primary key is a clustered index and secondary indexes contain only the indexed columns plus the primary key.

If the query columns are not covered, MySQL must first locate the primary keys via the secondary index and then perform a costly back‑track to fetch full rows.

Execution steps for a deep OFFSET query:

Enter the index range using the WHERE clause.

Scan rows in ORDER BY order.

Read the first offset + size rows.

Back‑track for rows whose columns are not covered.

Discard the first offset rows and return the last size rows.

Misconceptions:

Having an index does not let the engine jump directly to row N. LIMIT 1000000, 20 forces the engine to read and discard a million rows, not just slice a result set.

MySQL 8 improves many aspects but does not turn deep pagination into constant‑time work.

The cost is closer to

O(offset + size) + back‑track cost + sort cost + network cost

rather than O(size).

If the query cannot use an ordered index, MySQL adds Using filesort and possibly temporary files, further degrading performance.

Four Categories of Large‑Pagination Problems

Deep List Pagination – admin back‑office scrolling far back. Core bottleneck: scan + discard. Recommended: cursor pagination or delayed join.

Multi‑Condition Sort Pagination – orders, bills, transaction logs. Core bottleneck: compound sort + back‑track. Recommended: compound index + cursor pagination.

Retrieval‑type Pagination – search, filter, full‑text. Core bottleneck: sort + retrieval + deep paging. Recommended: Elasticsearch search_after.

Distributed Pagination – sharding, multi‑datasource aggregation. Core bottleneck: shard‑level aggregation sort. Recommended: ID‑list pagination or pre‑search.

Solution Overview

Covering Index + Column Pruning

Supports random page jumps.

Medium stability, low complexity.

Best for tables with few columns and low migration cost.

Delayed Join (Sub‑query for IDs)

Supports random page jumps.

Medium‑high stability, low complexity.

Ideal when page‑jump is required.

Cursor Pagination (Seek)

Does not support arbitrary jumps.

High stability, medium complexity.

Fits app feeds, order streams, high‑concurrency lists.

Elasticsearch search_after + MySQL lookup

Does not support arbitrary jumps.

High stability, high complexity.

Suitable for massive search, distributed aggregation.

1. Covering Index + Column Pruning

If all required columns are in the index, MySQL can return rows directly from the index leaf nodes without a back‑track.

CREATE INDEX idx_orders_tenant_ctime_id_cover ON orders (tenant_id, create_time DESC, id DESC, status, amount, order_no);

Query becomes:

SELECT id, order_no, status, create_time, amount
FROM orders
WHERE tenant_id = 1001
ORDER BY create_time DESC, id DESC
LIMIT 100000, 20;

No API changes required.

Effective for moderate depth pagination.

Limitations: wider index increases write cost; large columns (e.g., remark, json_detail) cannot be covered; offset still exists.

2. Delayed Join (Sub‑query for IDs)

Separate the heavy scan from the full‑row fetch: first fetch only primary keys using the index, then join back to retrieve the final page.

SELECT o.id, o.order_no, o.amount, o.status, o.create_time
FROM orders o
JOIN (
    SELECT id
    FROM orders FORCE INDEX (idx_orders_tenant_ctime_id)
    WHERE tenant_id = 1001
    ORDER BY create_time DESC, id DESC
    LIMIT 1000000, 20
) t ON o.id = t.id
ORDER BY o.create_time DESC, o.id DESC;

Only the last 20 rows need a back‑track.

Good fallback when random page jumps are required.

3. Cursor Pagination (Seek)

Instead of skipping rows, remember the last seen key values and continue scanning from there.

SELECT * FROM orders WHERE id > 1000000 ORDER BY id ASC LIMIT 20;

Because the B+Tree can locate the start point in O(logN) time, the cost becomes O(logN) + O(size), stable even for deep pages.

Prerequisite: a stable composite ordering key, e.g. (create_time, id) or (score, id), that is unique and indexable.

4. Production‑Ready Cursor API Design

Expose a single opaque token that encodes the last row’s ordering values, a filter hash and an optional version.

{
  "tenantId": 1001,
  "status": 1,
  "pageSize": 20,
  "cursor": "eyJjcmVhdGVUaW1lIjoiMjAyNi0wNS0xNlQxMjowMDowMCIsImlkIjo5ODc2NTQzMjF9"
}

Response includes items, nextCursor and hasMore. The cursor is Base64‑URL‑safe and contains createTime, id and a filterHash to prevent cross‑filter misuse.

{
  "items": [{
    "id": 987654300,
    "orderNo": "O202605160001",
    "status": 1,
    "amount": 19900,
    "createTime": "2026-05-16T11:59:59"
  }],
  "nextCursor": "eyJjcmVhdGVUaW1lIjoiMjAyNi0wNS0xNlQxMTo1OTo1OSIsImlkIjo5ODc2NTQzMDB9",
  "hasMore": true
}

Production Code (Spring Boot + MyBatis)

Table Definition & Indexes

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    tenant_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    order_no VARCHAR(64) NOT NULL,
    status TINYINT NOT NULL,
    amount BIGINT NOT NULL,
    create_time DATETIME(3) NOT NULL,
    update_time DATETIME(3) NOT NULL,
    remark VARCHAR(255) DEFAULT NULL,
    KEY idx_orders_tenant_ctime_id (tenant_id, create_time DESC, id DESC),
    KEY idx_orders_user_ctime_id (user_id, create_time DESC, id DESC),
    UNIQUE KEY uk_orders_order_no (order_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DTOs

package com.example.order.api.dto;

import jakarta.validation.constraints.Max;
import jakarta.validation.constraints.Min;
import jakarta.validation.constraints.NotNull;

public class OrderCursorPageRequest {
    @NotNull private Long tenantId;
    private Integer status;
    private String cursor;
    @Min(1) @Max(100) private Integer pageSize = 20;
    // getters & setters omitted for brevity
}

public class CursorPageResponse<T> {
    private List<T> items;
    private String nextCursor;
    private boolean hasMore;
    public CursorPageResponse(List<T> items, String nextCursor, boolean hasMore) {
        this.items = items;
        this.nextCursor = nextCursor;
        this.hasMore = hasMore;
    }
    // getters omitted
}

Cursor Object & Codec

package com.example.order.domain.pagination;

import java.time.LocalDateTime;

public class OrderCursor {
    private int v;
    private LocalDateTime createTime;
    private Long id;
    private String filterHash;
    // getters & setters omitted
}

public class CursorCodec {
    private final ObjectMapper objectMapper;
    public CursorCodec(ObjectMapper om) { this.objectMapper = om; }
    public String encode(OrderCursor cursor) {
        try {
            String json = objectMapper.writeValueAsString(cursor);
            return Base64.getUrlEncoder().withoutPadding().encodeToString(json.getBytes(StandardCharsets.UTF_8));
        } catch (JsonProcessingException e) {
            throw new IllegalStateException("Failed to encode cursor", e);
        }
    }
    public OrderCursor decode(String token) {
        try {
            byte[] bytes = Base64.getUrlDecoder().decode(token);
            return objectMapper.readValue(bytes, OrderCursor.class);
        } catch (Exception e) {
            throw new IllegalArgumentException("Invalid cursor token", e);
        }
    }
    public String filterHash(Long tenantId, Integer status) {
        String raw = tenantId + ":" + (status == null ? "null" : status);
        try {
            MessageDigest digest = MessageDigest.getInstance("SHA-256");
            byte[] hash = digest.digest(raw.getBytes(StandardCharsets.UTF_8));
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < 4; i++) sb.append(String.format("%02x", hash[i]));
            return sb.toString();
        } catch (NoSuchAlgorithmException e) {
            throw new IllegalStateException("SHA-256 not available", e);
        }
    }
}

MyBatis Mapper

<select id="selectPageByCursor" resultType="com.example.order.infrastructure.repository.model.OrderListRow">
    SELECT id, order_no, status, amount, create_time
    FROM orders
    <where>
        tenant_id = #{tenantId}
        <if test="status != null">AND status = #{status}</if>
        <if test="cursor != null">
            AND (create_time < #{cursor.createTime}
                 OR (create_time = #{cursor.createTime} AND id < #{cursor.id}))
        </if>
    </where>
    ORDER BY create_time DESC, id DESC
    LIMIT #{limitPlusOne}
</select>

Note the use of LIMIT pageSize + 1 to detect hasMore without an extra COUNT(*).

Service Layer

@Service
public class OrderQueryService {
    private final OrderMapper orderMapper;
    private final CursorCodec cursorCodec;
    public OrderQueryService(OrderMapper orderMapper, CursorCodec cursorCodec) {
        this.orderMapper = orderMapper;
        this.cursorCodec = cursorCodec;
    }
    @Transactional(readOnly = true)
    public CursorPageResponse<OrderListRow> pageByCursor(OrderCursorPageRequest req) {
        String expectedHash = cursorCodec.filterHash(req.getTenantId(), req.getStatus());
        OrderCursor decoded = decodeAndValidateCursor(req.getCursor(), expectedHash);
        int limit = req.getPageSize();
        int limitPlusOne = limit + 1;
        List<OrderListRow> rows = orderMapper.selectPageByCursor(
                req.getTenantId(), req.getStatus(), decoded, limitPlusOne);
        boolean hasMore = rows.size() > limit;
        List<OrderListRow> pageItems = hasMore ? rows.subList(0, limit) : rows;
        String nextCursor = null;
        if (!pageItems.isEmpty() && hasMore) {
            OrderListRow last = pageItems.get(pageItems.size() - 1);
            OrderCursor next = new OrderCursor();
            next.setV(1);
            next.setCreateTime(last.getCreateTime());
            next.setId(last.getId());
            next.setFilterHash(expectedHash);
            nextCursor = cursorCodec.encode(next);
        }
        return new CursorPageResponse<>(pageItems, nextCursor, hasMore);
    }
    private OrderCursor decodeAndValidateCursor(String token, String expectedHash) {
        if (token == null || token.isBlank()) return null;
        OrderCursor cursor = cursorCodec.decode(token);
        if (!expectedHash.equals(cursor.getFilterHash())) {
            throw new IllegalArgumentException("Cursor does not match current filters");
        }
        return cursor;
    }
}

Engineering Considerations

Consistency : Cursor pagination is not a snapshot; new inserts may appear on earlier pages, deletions may disappear, and updates to the sort key can shift rows. For user‑facing feeds weak consistency is acceptable; for audit use a frozen snapshot.

Total Count : Avoid returning exact total for massive tables. Prefer hasMore and provide an approximate count only when needed.

Rate Limiting & Page‑Size Guard : Default pageSize = 20, maximum 100 (or 200), reject larger values, and apply per‑user or per‑tenant limits for deep page requests.

Distributed Pagination : Global OFFSET across shards forces each shard to return large candidate sets, causing memory, network and merge‑sort overload. Instead, retrieve a sorted ID list (via ES or a dedicated index service) and batch‑fetch details per shard, preserving order in the application layer.

Map<Long, OrderDetail> detailMap = details.stream()
    .collect(Collectors.toMap(OrderDetail::getId, Function.identity()));
List<OrderDetail> ordered = ids.stream()
    .map(detailMap::get)
    .filter(Objects::nonNull)
    .toList();

High‑Concurrency Architecture Upgrade :

API Gateway → Query Service → Elasticsearch ( search_after) → MySQL shard lookup → Re‑order → Response.

Limit and degrade deep‑page requests at the edge.

Separate read‑only traffic to replica pools.

Cache hot ID lists instead of full pages.

Migration Roadmap

Replace SELECT * with column‑specific queries and add covering indexes.

Convert high‑frequency user‑side lists to cursor pagination.

Rewrite admin deep‑page endpoints to use delayed‑join pagination.

Move export jobs to asynchronous tasks with snapshot IDs.

When data volume or query complexity grows, introduce Elasticsearch search_after + MySQL back‑lookup.

Final Takeaways

Large pagination problems are solved by three actions:

Understand that the cost is scan‑and‑discard, not just row return.

Choose the right technique (covering index, delayed join, cursor, or ES) based on the four problem categories.

Close the engineering loop with API contracts, cursor tokens, rate limits, read‑only transactions and proper monitoring.

Small tables use page numbers; big tables use cursors. Admin panels get a delayed‑join fallback. Search‑heavy workloads delegate pagination to ES. Exports run asynchronously, and distributed setups rely on a layered query architecture.

Checklist before release:

Sorting fields form a strict total order.

Composite index matches WHERE + ORDER BY.

No SELECT * on list endpoints.

Cursor includes a filter hash.

Enforce a reasonable pageSize limit.

Separate online queries from export tasks.

Rate‑limit or degrade deep‑page requests.

Set alerts for replica lag, slow queries and connection‑pool wait times.