Why Your Deep Pagination Is Slow: LIMIT OFFSET vs Keyset Pagination in Spring Boot

1. Deep Pagination Crashed the Database

One Friday afternoon, an operations colleague opened page 10,000 of the order list. The page spun for over 10 seconds, the frontend threw 504 errors, and database CPU spiked to 98%. The culprit was this SQL:

SELECT *
FROM orders
ORDER BY id
LIMIT 20 OFFSET 199980;

The orders table had 2.8 million rows, sharded across 64 tables. A primary key index existed, but OFFSET 199980 forced MySQL to scan the first 199,980 rows, discard them, and keep only the last 20. This operation is unsustainable at scale.

Similar issues appear in consumer-facing systems: message lists, news feeds, and feed streams. Once users scroll dozens of pages, response time grows exponentially. Many blame the database, but the real problem is the pagination approach.

2. EXPLAIN Reveals the Scan Problem

To understand the issue, a test table was created:

CREATE TABLE `orders` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` bigint NOT NULL,
  `order_no` varchar(64) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `status` tinyint NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_created_at_id` (`created_at`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

With 1 million test rows, a deep pagination query (page 10,000, 20 per page, ordered by created_at DESC, id DESC) was analyzed with EXPLAIN:

EXPLAIN
SELECT *
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 199980;

The plan showed type: index using the secondary index idx_created_at_id, with an estimated 200,000 rows scanned. The index matches the ORDER BY, so no filesort is needed. However, the query selects *, and the index lacks columns like user_id and amount. Each index record requires a primary key lookup (random I/O) to fetch the full row. Scanning 200,000 index entries means 200,000 random I/Os, only to discard all but 20 rows.

The larger the OFFSET, the more rows the database must scan and discard. The essence of slow deep pagination is that the database spends most effort scanning and throwing away useless rows instead of directly locating the target range.

3. Common Pagination Approaches Compared

1. Traditional LIMIT OFFSET

Simple and supports random page jumps. Performance collapses as OFFSET grows due to full scans and lookups. Fine for small admin lists, but dangerous at scale.

2. Subquery / Delayed Join

First fetch primary keys via a covering index, then join back to the table:

SELECT o.*
FROM orders o
JOIN (
  SELECT id
  FROM orders
  ORDER BY created_at DESC, id DESC
  LIMIT 20 OFFSET 199980
) tmp ON o.id = tmp.id
ORDER BY o.created_at DESC, o.id DESC;

The inner query avoids lookups, reducing I/O. However, it still scans OFFSET index entries. The optimizer may materialize the subquery into a temporary table, causing unstable performance. A temporary fix, not a complete solution.

3. Covering Index

If the query only needs columns present in the index (e.g., id, created_at), no lookup occurs:

SELECT id, created_at
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 199980;

Speed improves significantly. But real-world lists need many columns. Adding all columns to the index bloats it and hurts write performance. OFFSET scanning remains unchanged. Suitable only for narrow queries and shallow pagination.

4. Keyset Pagination (Cursor Pagination)

Instead of skipping rows with OFFSET, remember the last row's sort position and use a WHERE clause to seek directly:

SELECT *
FROM orders
WHERE (created_at, id) < ('2024-05-01 10:00:00', 123456)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The composite index (created_at, id) allows MySQL to seek to the cursor position and read the next 20 rows. Regardless of page depth, only 20 rows are scanned, yielding constant response time. The trade-off: no random page jumps. But most consumer apps use infinite scroll, making this a non-issue.

4. Implementing Keyset Pagination Step by Step

1. Core Principle

Given the last row of the previous page (created_at = '2024-05-01 10:00:00', id = 123456), the next page condition is:

WHERE created_at < '2024-05-01 10:00:00'
   OR (created_at = '2024-05-01 10:00:00' AND id < 123456)

Using MySQL's row constructor syntax is more concise:

WHERE (created_at, id) < ('2024-05-01 10:00:00', 123456)

MySQL matches this against the ordered index for direct positioning.

Critical requirement: sort columns must be unique. If only created_at is used, duplicate timestamps cause missed rows. Adding the primary key id as a tiebreaker ensures stability.

2. Single-Column Sort (Primary Key Only)

For simple ORDER BY id DESC:

public class OrderQuery {
    private Long lastId; // null for first page
    private Integer pageSize = 20;

    public String buildWhere() {
        if (lastId == null) return "";
        return "WHERE id < " + lastId;
    }

    public String buildOrderLimit() {
        return "ORDER BY id DESC LIMIT " + pageSize;
    }
}

Eliminates deep pagination but only supports sequential access.

3. Multi-Column Sort (General Case)

Typical sort: ORDER BY created_at DESC, id DESC. Define a cursor object:

public class OrderCursor {
    private LocalDateTime createdAt;
    private Long id;
    // getter / setter / constructor
}

MyBatis Mapper method to build the WHERE clause:

public class OrderMapper {
    public String buildCursorCondition(OrderCursor cursor) {
        if (cursor == null) {
            return "";
        }
        return "WHERE (created_at < #{cursor.createdAt}) "
             + "OR (created_at = #{cursor.createdAt} AND id < #{cursor.id})";
    }
}

Corresponding MyBatis XML:

<select id="selectByCursor" resultType="Order">
  SELECT *
  FROM orders
  <if test="cursor != null">
    WHERE (created_at &lt; #{cursor.createdAt})
      OR (created_at = #{cursor.createdAt} AND id &lt; #{cursor.id})
  </if>
  ORDER BY created_at DESC, id DESC
  LIMIT #{pageSize}
</select>

Service layer usage: first page passes null, then extract the last record to build the next cursor.

public List<Order> page(OrderCursor cursor, int pageSize) {
    return orderMapper.selectByCursor(cursor, pageSize);
}

4. Mixed Sort Directions: Avoid If Possible

If sort order mixes directions (e.g., created_at DESC, id ASC), cursor conditions become awkward and index usage suffers. Best practice: unify sort direction or negotiate with product to change requirements.

5. Incremental Sync Use Case

Keyset pagination excels at incremental data synchronization. For example, syncing orders to a data warehouse: initial full sync, then store the last record's id or updated_at in a metadata table. Subsequent runs fetch from that cursor, guaranteeing no duplicates or gaps.

5. Benchmark: Traditional LIMIT Crushed

Local test with 1 million rows (MySQL 8.0.33, Apple M1 Pro). Four approaches measured at increasing page offsets:

Page 1: all ~10ms

Page 1,000: traditional LIMIT 86ms, Keyset 12ms

Page 5,000: traditional LIMIT 348ms, subquery 104ms, Keyset 15ms

Page 10,000: traditional LIMIT 830ms, covering index 490ms, subquery 215ms, Keyset 18ms

Page 50,000: traditional LIMIT 4.2s, covering index 2.1s, subquery 1.1s, Keyset 22ms

Traditional LIMIT scales linearly with offset; Keyset stays stable around 20ms. The gap is fundamental to access path, not tunable parameters. Benchmark code used JMH; omitted for brevity.

6. Integration with MyBatis-Plus and Spring Data JPA

MyBatis-Plus

The default PaginationInnerInterceptor only supports LIMIT OFFSET. Custom Mapper required:

public interface OrderMapper extends BaseMapper<Order> {
    List<Order> selectPageByCursor(
        @Param("cursor") OrderCursor cursor,
        @Param("pageSize") int pageSize);
}

XML with row constructor (MySQL syntax; PostgreSQL uses (created_at, id) < (?, ?)):

<select id="selectPageByCursor" resultType="Order">
  SELECT *
  FROM orders
  <where>
    <if test="cursor != null">
      <if test="cursor.createdAt != null">
        (created_at, id) &lt; (#{cursor.createdAt}, #{cursor.id})
      </if>
    </if>
  </where>
  ORDER BY created_at DESC, id DESC
  LIMIT #{pageSize}
</select>

Service method returns records and next cursor:

public PageResult<Order> listByCursor(OrderCursor cursor, int pageSize) {
    List<Order> records = orderMapper.selectPageByCursor(cursor, pageSize);
    OrderCursor nextCursor = null;
    if (!records.isEmpty()) {
        Order last = records.get(records.size() - 1);
        nextCursor = new OrderCursor(last.getCreatedAt(), last.getId());
    }
    return new PageResult<>(records, nextCursor);
}

Spring Data JPA

Use Specification for dynamic predicates:

public class OrderSpecs {
    public static Specification<Order> byCursor(OrderCursor cursor) {
        return (root, query, cb) -> {
            if (cursor == null) {
                return cb.conjunction();
            }
            Path<LocalDateTime> createdAt = root.get("createdAt");
            Path<Long> id = root.get("id");
            return cb.or(
                cb.lessThan(createdAt, cursor.getCreatedAt()),
                cb.and(
                    cb.equal(createdAt, cursor.getCreatedAt()),
                    cb.lessThan(id, cursor.getId())
                )
            );
        };
    }
}

Repository extends JpaSpecificationExecutor:

public PageResult<Order> listByCursor(OrderCursor cursor, int pageSize) {
    Specification<Order> spec = OrderSpecs.byCursor(cursor);
    List<Order> records = orderRepository.findAll(spec,
        PageRequest.of(0, pageSize, Sort.by(
            Sort.Order.desc("createdAt"),
            Sort.Order.desc("id")
        ))).getContent();
    // build nextCursor...
}

Generic Component

For reuse across entities, define a generic holder:

public class CursorPage<T, C> {
    private List<T> records;
    private C nextCursor; // null means no more data
}

Pass a Function<T, C> to extract the cursor from the last record, letting callers define sort fields.

7. Elasticsearch search_after for Search Scenarios

Relational databases handle Keyset well, but complex search, full-text, and aggregation require Elasticsearch. ES has the same deep pagination problem with from + size (scatter-gather across shards). The official solution is search_after, a distributed Keyset equivalent.

ES query with sort:

{
  "size": 20,
  "query": { "match_all": {} },
  "sort": [
    { "created_at": "desc" },
    { "id": "desc" }
  ]
}

Response includes a sort array per hit:

"hits": [
  {
    "_id": "123",
    "_source": { "created_at": "2024-05-01 10:00:00", "id": 123 },
    "sort": ["2024-05-01 10:00:00", 123]
  }
]

Next page passes the last hit's sort values to search_after:

{
  "size": 20,
  "query": { "match_all": {} },
  "sort": [
    { "created_at": "desc" },
    { "id": "desc" }
  ],
  "search_after": ["2024-05-01 10:00:00", 123]
}

Same concept as database cursors, but ES requires the full sort composite to guarantee global ordering across shards. Note: search_after cannot support random page jumps. For UI requiring jumps, restrict from+size to shallow pages. The deprecated scroll API is for large exports, not real-time queries.

8. Summary and Best Practices

LIMIT OFFSET

becomes a bottleneck once data reaches millions. Keyset Pagination replaces expensive scans with a simple WHERE + sort columns predicate, keeping deep pagination latency constant. Every developer should master this pattern.

Assess whether random page jumps are needed. Consumer apps typically use infinite scroll — Keyset fits perfectly. Admin panels with small datasets can keep traditional pagination.

Sort columns must be unique. Common pattern: business timestamp + primary key to ensure stable ordering and avoid missing rows.

Cursor pagination does not replace search pagination. For complex queries, sync data to Elasticsearch and let ES handle search; MySQL remains the system of record.

Encapsulate into a shared component. Build a CursorPage abstraction on top of MyBatis-Plus/JPA so the team can reuse cursor logic without rewriting.

Load-test before deploying. Use JMH or JMeter with near-production data volumes to verify TP99 latency, then decide on pagination strategy.

Deep pagination optimization boils down to one principle: don't make the database scan rows it will throw away. Understanding index structures and access paths is essential to avoid being misled by superficial "optimizations." This article aims to help you sidestep these pitfalls and keep your APIs stable under millions of rows.