Mastering MySQL Large-Scale Pagination: Ultimate Optimized Techniques

Why Large Offsets Are Slow

Traditional LIMIT offset, size forces MySQL to scan and discard rows up to the offset, so the number of scanned rows grows linearly with the offset. This often triggers filesort or temporary tables, causing exponential performance loss.

Traditional Pagination Example

SELECT * FROM orders ORDER BY id LIMIT 10000000, 10;

MySQL scans 10,000,010 rows, discards the first 10,000,000, and returns only 10 rows.

Larger offsets therefore lead to dramatically slower queries.

Database‑Level Solutions

Solution 1: Delayed Join (Classic Large‑Pagination Optimization)

SELECT *
FROM orders o
INNER JOIN (
    SELECT id
    FROM orders
    ORDER BY id
    LIMIT 10000000, 10
) t ON o.id = t.id;

The subquery uses a covering index, avoiding a full row lookup.

The outer query processes only the 10 required rows, drastically reducing scan cost.

Solution 2: Covering Index + Seek Pagination (Recommended)

CREATE INDEX idx_covering ON orders(id, user_id, created_at);
SELECT id, user_id, created_at
FROM orders
WHERE id > #{lastId}
ORDER BY id
LIMIT #{size};

No rows are discarded.

Query complexity stays O(1).

Even tables with tens of millions of rows respond in milliseconds.

Solution 3: Cursor Pagination (Most Recommended)

-- First page
SELECT * FROM orders ORDER BY id LIMIT 10;

-- Next page (lastId = 100)
SELECT * FROM orders WHERE id > 100 ORDER BY id LIMIT 10;

Provides linear scalability and completely avoids the waste of OFFSET.

Solution 4: MySQL 8.x Window Functions

WITH ordered AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM orders
)
SELECT *
FROM ordered
WHERE rn BETWEEN 10000001 AND 10000010;

Allows paging on any sort column.

Still performs a full scan for very large offsets.

Solution 5: CTE + Window Function with Pre‑Filtering

WITH p AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM orders
    WHERE status = 'completed'
)
SELECT o.*
FROM orders o
JOIN p ON o.id = p.id
WHERE p.rn BETWEEN 10000001 AND 10000010;

Pre‑filtering reduces the amount of data processed by the window function.

Suitable for scenarios that require complex filters together with paging.

Engineering & Business‑Layer Optimizations

Pagination Depth Limit

const MAX_PAGE = 100;
if (page > MAX_PAGE) {
    return showMessage("Please use filter criteria to narrow the range");
}

Inform users that pages beyond this limit are disallowed and encourage the use of filters or search instead of deep paging.

Force Filter Conditions

Typical filters include date range, status, user ID, type, and tags. Applying them reduces the candidate row count dramatically, making pagination controllable.

Infinite Scroll (Load‑More) Replacement

let lastId = 0;
async function loadMore() {
    const res = await fetch(`/api/orders?lastId=${lastId}`);
    const data = await res.json();
    lastId = data.lastId;
}

Leverage Search Engines (Elasticsearch / OpenSearch)

Elasticsearch Scroll API

Search‑After (recommended)

Provides stable, high‑performance paging for tens of millions of records.

Data Archiving + Partitioned Tables

Partition tables by date so queries hit only the relevant partition.

CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Query touches a single partition instead of the whole table.

Enables hot‑cold data separation.

Monitoring & Performance Analysis

Enable Slow Query Log

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;

Use EXPLAIN to Detect Filesort / Temporary Table

EXPLAIN ANALYZE
SELECT * FROM orders ORDER BY id LIMIT 10000000, 10;

Check the rows count and whether Using filesort or Using temporary appear.

Production Best‑Practice Summary

List paging: Cursor pagination.

Random‑jump paging required: Delayed join.

Huge data with search needs: Elasticsearch Search‑After.

Query recent data: Partitioned tables.

Massive data volume: Archiving + partitioning.

Backend load control: Limit maximum page number.

Final Takeaway

Ultra‑deep paging (e.g., one million pages) is usually a product‑design issue, not a performance problem. The proper solution is to avoid massive OFFSET altogether.

Primary recommendation: Cursor pagination .

If deep paging is unavoidable: use delayed join .

Enforce filter conditions at the business layer.

For higher demands, adopt Elasticsearch solutions.