Enable Paging Cache to Optimize Deep Pagination - AnalyticDB for MySQL

Deep paged queries degrade sharply in distributed databases: each page flip forces every storage node to re-sort and return millions of rows, causing high CPU usage, high memory usage, and out-of-memory (OOM) errors. Paging cache solves this by storing the full result set in a temporary cache table on the first query. All subsequent pages are served from that cache — no repeated sorting, no distributed data shuffling.

How it works

A common pagination pattern uses LIMIT and OFFSET with ORDER BY:

-- Page 1: 100 rows starting at offset 0
SELECT * FROM t_order ORDER BY id LIMIT 0, 100;

-- Page 10,001: 100 rows starting at offset 1,000,000
SELECT * FROM t_order ORDER BY id LIMIT 1000000, 100;

In AnalyticDB for MySQL, this pattern is costly. To guarantee a correct global sort, each storage node must return rows from offset 0 through the requested offset — not just the 100 rows you need. For the page-10,001 query, every storage node executes SELECT * FROM t_order ORDER BY id LIMIT 0, 1000100. The Gather node then sorts 1,000,100 × (number of storage nodes) rows to produce 100 results.

The amount of sorted data grows linearly with pagination depth, causing sharp performance degradation, high CPU and memory consumption, and an elevated OOM risk.

Paging cache uses a cache-on-first-query approach:

The first paged query hits the database and builds a temporary cache table from the full result set.
Subsequent queries with the same SQL pattern read directly from the cache table — no repeated sorting or distributed shuffling.
AnalyticDB for MySQL automatically evicts cache tables that are no longer needed based on its eviction policy.

Use cases

Use case	How paging cache helps
Large-scale data export	In distributed environments, `LIMIT` + `OFFSET` without `ORDER BY` does not guarantee consistent ordering, so different page requests may return duplicate or missing rows. Paging cache lets you remove the `ORDER BY` clause while still getting consistent pages, significantly reducing response time and OOM risk.
Paged display of full data	The full result set is cached in AnalyticDB for MySQL's hot storage, so each page flip is served from cache with no repeated database access.
Report concurrency control	When multiple users query the same report simultaneously, each independent query adds load to the cluster. With paging cache, a single query populates the cache within a time window — all concurrent requests read from the same cache table.

Prerequisites

Before you begin, ensure that you have:

An AnalyticDB for MySQL cluster running V3.2.3 or later

To view or update the minor version of your cluster, go to the AnalyticDB for MySQL console, open the Cluster Information page, and check the Configuration Information section.

Limits

Limit	Details
Maximum result set size	The result set (after removing `LIMIT` and `OFFSET`) must contain fewer than 100 million rows. To raise this limit, Submit a ticket.
Cache storage	Cache data is stored in AnalyticDB for MySQL's hot storage space. Clear unused cache tables to free storage when needed.
Cache database	External databases cannot be used as the cache database.
`paging_id` naming rules	1–127 characters, letters/digits/underscores only, must start with a letter or underscore, no single quotes, double quotes, exclamation points, or spaces, and cannot be an SQL reserved word.

Configure a cache database

Specify a dedicated internal database to store temporary cache tables. If you skip this step, cache tables are stored in the currently connected internal database.

SET ADB_CONFIG PAGING_CACHE_SCHEMA=paging_cache;

Replace paging_cache with the name of the internal database you want to use.

Enable paging cache

Add a hint to your SELECT statement to enable paging cache. Choose the method that fits your use case:

Method	When to use	Trade-off
`/paging_cache_enabled=true/`	Most scenarios — the server automatically generates a paging ID from the SQL pattern	The SQL pattern (excluding `LIMIT`/`OFFSET`) must match exactly across requests
`/paging_id=<paging_id>/`	When you need explicit control over cache grouping, or when the SQL pattern varies	Requires the client to generate and track a unique ID per cache group

Method 1: paging_cache_enabled

The server derives the paging ID from the SQL pattern with LIMIT and OFFSET removed. Any query with the same base SQL pattern hits the same cache table automatically.

/*paging_cache_enabled=true*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;

If the cache table does not exist, the server creates it on this first query. Subsequent queries with the same SQL pattern read from the cache table.

If a cache table fails to be created, clear the cache data of paged queries and re-run the query to create a new cache table.

Method 2: paging_id

Assign an explicit paging ID to a cache group. The client generates a unique ID to identify a set of paged queries that share the same SQL pattern but use different LIMIT and OFFSET values.

/*paging_id=paging123*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;

Cache lookup behavior:

If paging123 does not exist, a cache table is created.
If paging123 exists and the SQL pattern matches, the query hits the cache.
If paging123 exists but the SQL pattern does not match, an error is returned. Check the cache table information to see which SQL pattern is associated with the paging ID.

Inspect cache tables

Query cache table details — including paging ID, cache size, and cache status — for all paged queries in the current cluster:

SELECT * FROM INFORMATION_SCHEMA.KEPLER_PAGING_CACHE_STATUS_MERGED;

Configure cache behavior

Set the maximum number of cache tables

Default: 100. Adjust this limit based on your workload.

SET ADB_CONFIG PAGING_CACHE_MAX_TABLE_COUNT=100;

If the total number of cache tables reaches the limit, creating a new cache table returns the following error:

Paging cache count exceeds the limit. Please clean up unused caches or increase the related parameter using SET ADB_CONFIG PAGING_CACHE_MAX_TABLE_COUNT=xxx.

Clear unused cache tables or increase the limit to resolve this error.

Set a validity period for a cache table

A validity period specifies how long a cache table remains valid after creation. After the validity period expires, the next matching query refreshes the cache from the database. This is useful in report concurrency control scenarios where data needs to stay fresh within a defined window.

/*paging_cache_enabled=true, paging_cache_validity_interval=300*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;

This example sets a validity period of 300 seconds.

Set the cache expiration time

Specify how long a cache table can remain unaccessed before it is automatically cleared. Default: 600 seconds (10 minutes).

SET ADB_CONFIG PAGING_CACHE_EXPIRATION_TIME=600;

Clear cache data

Cache data is stored in AnalyticDB for MySQL's hot storage. Clear cache tables that are no longer needed to reclaim storage.

Clear cache manually

By SQL pattern — clear the cache for all queries matching the current SQL pattern:

/*paging_cache_enabled=true,invalidate_paging_cache=true*/ SELECT * FROM t_order ORDER BY id LIMIT 0, 100;

By paging ID — clear the cache for a specific paging ID:

CLEAN_PAGING_CACHE paging123;

To look up the paging ID for a cache table, query the KEPLER_PAGING_CACHE_STATUS_MERGED view described in Inspect cache tables.

Clear cache automatically

Cache tables that are not accessed within the cache expiration time are cleared automatically. Set the expiration time with SET ADB_CONFIG PAGING_CACHE_EXPIRATION_TIME.

Disable paging cache

To disable paging cache cluster-wide, all hints related to paging cache become invalid and queries fall back to standard execution:

SET ADB_CONFIG PAGING_CACHE_ENABLE=false;

Verify the change:

SHOW ADB_CONFIG KEY=PAGING_CACHE_ENABLE;

Troubleshooting

Paging cache prepare failed, and cache is not available

Error message:

Paging cache prepare failed, and cache is not available. Please use /*paging_cache_enabled=true,invalidate_paging_cache=true*/ to clean the unavailable cache or set a specific pagingId with /*paging_id=xxx*/ to gen a new cache. Note that the old and new cache data may be inconsistent.

Cause: When you use the paging cache feature to query data, exceptions may occur, such as node restarting and scaling. If a paged query hits a cache table that fails to be created, the server does not perform database access or automatically re-create a cache table. The server throws an error.

Resolution:

Data export scenarios: Clear the exported data and the invalid cache, then re-run the paged query to rebuild the cache table. This ensures the re-exported data is consistent.
Other scenarios: Clear the invalid cache using /*paging_cache_enabled=true,invalidate_paging_cache=true*/, or assign a new paging_id value, then re-run the query to create a new cache table.

Performance benchmark

The following test uses a 100 GB TPC-H dataset to compare paged query performance in a data export scenario.

Test setup: 1 million exported rows, 100,000 rows per page, single concurrency mode.

-- Standard paged query (no paging cache)
SELECT * FROM lineitem ORDER BY l_orderkey, l_linenumber LIMIT 0, 100000;

-- Paged query with paging cache enabled (ORDER BY eliminated in the data export scenario)
/*paging_cache_enabled=true*/ SELECT * FROM lineitem LIMIT 0, 100000;

Result: Average response time dropped from 54,391 ms to 525 ms — approximately 103x faster. CPU utilization and memory usage were also significantly reduced.

The performance improvement comes from eliminating repeated top-N calculations across all storage nodes on every page flip. Once the cache is populated, each page request reads sequentially from the cache table rather than re-sorting the entire dataset.