Result set cache
The result set cache stores query results in local storage on the AnalyticDB for MySQL access layer. When the same query runs again and the underlying data has not changed, AnalyticDB for MySQL returns the cached result directly—skipping recomputation and reducing cluster load.
The cache is most effective for OLAP workloads where data is loaded in batches and the same queries repeat frequently within each stable window. Because result set caching uses eventual consistency (not strong consistency), cached results are always evicted within 10 seconds of a data update, making it safe to use even when data changes periodically.
Prerequisites
Before you begin, confirm that your cluster meets the kernel version requirements:
| Table engine | Minimum kernel version |
|---|---|
| XUANWU | 3.2.1 |
| XUANWU_V2 | 3.2.3.11 |
To view and update the minor version of a cluster, log on to the AnalyticDB for MySQL console and go to the Configuration Information section of the Cluster Information page.
How it works
Query results are stored in the local storage of the access layer. On a repeated query, AnalyticDB for MySQL checks whether the data in the associated tables has changed since the result was cached. If the data is unchanged, the cached result is returned directly to the client.
Consistency model
The result set cache provides eventual consistency, not strong consistency.
For OLAP workloads, this is the right trade-off: data is typically loaded in batches, not updated row-by-row in real time. Queries repeat frequently within each stable window. When data does change, the related cache is always evicted within 10 seconds—after which the next query populates a fresh cache entry.
The reason for this behavior is architectural: in the multi-primary architecture, data updates propagate from the storage layer to the access layer, and then between inner nodes of the access layer. This propagation can take up to 10 seconds.
If your workload requires strong consistency, do not use the result set cache.
Cache hit requirements
A query uses the result set cache only if all of the following conditions are met:
| Condition | Detail |
|---|---|
| Identical SQL statement | The syntax of the query statement must be identical to that of a previously cached query statement. |
| Data unchanged | No insertions, deletions, or updates to the associated tables since the result was cached. |
| No non-deterministic functions | Functions such as NOW or CURRENT_TIMESTAMP prevent caching. |
| No external or system tables | Queries that reference external data sources or internal system tables are not cached. |
| Access control compliance | When blacklist or whitelist mode is enabled, the tables must comply with the configured controls. |
| Result set within row limit | By default, only result sets with 10,000 rows or fewer are cached. |
| Query duration exceeds 1 second | Queries that complete in under 1 second are not cached. |
| No Common Table Expression (CTE) subquery | Queries using CTE subqueries are not cached. |
Enable the result set cache
Use a hint to enable the cache for a single query, or set a cluster-level configuration to apply it to all queries.
Enable for a specific query
Add the /*+result_cache=true*/ hint before the SELECT keyword:
/*+result_cache=true*/ SELECT * FROM tpcds.catalog_returns WHERE cr_item_sk < 100;Enable for all queries
Set the cluster-level configuration:
SET ADB_CONFIG RESULT_CACHE_APPLY_ALL=true;Enabling the cache at the cluster level causes every query to attempt caching, which increases system load—especially in high-concurrency scenarios. Configure blacklist and whitelist access controls after enabling cluster-level caching to limit which tables are cached.
Control which tables use the cache
Cluster-level caching applies to all queries and can add significant overhead. Per-query hints are fine-grained but require changes to every query. Blacklist and whitelist access controls offer a middle ground: set the mode once, then tag individual tables.
Set the access control mode
SET ADB_CONFIG RESULT_CACHE_LIST_CONSTRAINT_MODE=[whitelist | blacklist | disable];| Value | Behavior |
|---|---|
disable (default) | Access controls are not applied. The RESULT_CACHE table property has no effect. |
blacklist | Tables tagged disable are excluded from caching. All other tables that meet the requirements are cached. |
whitelist | Only tables explicitly tagged enable are cached. |
Tag a table
ALTER TABLE <table_name> RESULT_CACHE=[default | enable | disable];| Value | Blacklist mode | Whitelist mode |
|---|---|---|
default | Caching allowed | Caching not allowed |
enable | Caching allowed | Caching allowed |
disable | Caching not allowed | Caching not allowed |
View tables with access controls configured
SELECT * FROM INFORMATION_SCHEMA.RESULT_CACHE_LIST_CONSTRAINT;Configure cache size and eviction
Row count limit
By default, result sets with more than 10,000 rows are not cached. Adjust the threshold:
SET ADB_CONFIG RESULT_CACHE_MAX_ROW_COUNT = 10000;Eviction policies
AnalyticDB for MySQL automatically evicts cached results using two policies. A cached result is evicted when either condition is met.
Timeout-based eviction
Caches not accessed within the timeout period are evicted. The default timeout is 86,400 seconds (24 hours).
SET ADB_CONFIG RESULT_CACHE_EXPIRATION_TIME = 86400;Maximum cache count-based eviction
When the number of cached results on an access node exceeds the limit, the least recently used cache is evicted. The default limit is 100.
SET ADB_CONFIG RESULT_CACHE_MAX_CACHE_COUNT = 100;View and monitor cached results
List all cached result sets
SELECT * FROM INFORMATION_SCHEMA.RESULT_CACHE_STATUS;