Syntax of AnalyticDB for MySQL cache tables-AnalyticDB(AnalyticDB)-阿里云帮助中心

Cache tables store query results in AnalyticDB for MySQL so you can query the same result set repeatedly without re-running the original query.

Prerequisites

Before you begin, ensure that you have:

An AnalyticDB for MySQL cluster running V3.2.0 or later

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

How it works

Cache tables work like temporary tables in traditional databases. Submit a query, store the result set in a cache table, then query the cached data as many times as needed — without re-executing the original query each time.

AnalyticDB for MySQL stores hot data from cache tables in the cluster's storage space and automatically deletes unused cache tables based on eviction policies.

Cache tables vs. standard tables

Dimension	Cache table	Standard table
Creation method	`CREATE TABLE AS SELECT (CTAS)` only	Multiple DDL methods
Post-creation changes	Read-only — cannot be modified after creation	Fully mutable
Lifecycle	Automatically deleted after the expiration period elapses without access	Persists until explicitly dropped
Expiration period	Configurable via `TEMP_TABLE_EXPIRATION_TIME` (default: 3,600 seconds)	Not applicable
Row limit	Configurable via `TEMP_TABLE_MAX_ROW_COUNT` (default: 300,000 rows)	No built-in row limit
Allowed databases	Any existing database except external databases	Any database

Create a cache table

Cache tables are created using CREATE TABLE AS SELECT (CTAS) statements. Specify ENGINE='CACHE' to create a cache table instead of a standard table.

The following example sorts the adb_demo.customer table by age and stores the result in a cache table named c1:

-- (Optional) Create a dedicated database to manage cache tables.
CREATE DATABASE IF NOT EXISTS caches;

-- Create a cache table using CTAS.
CREATE TABLE caches.c1 ENGINE='CACHE'
    AS SELECT * FROM adb_demo.customer ORDER BY age LIMIT 9999;

Create a dedicated database to manage cache tables. Any existing database works except external databases.

After creation, the cache table expires automatically if it is not accessed within 1 hour (3,600 seconds). To extend the expiration period, configure TEMP_TABLE_EXPIRATION_TIME before running the CTAS statement:

-- Set expiration period to 7,200 seconds (2 hours).
SET ADB_CONFIG TEMP_TABLE_EXPIRATION_TIME=7200;

To limit the number of rows stored in a cache table, configure TEMP_TABLE_MAX_ROW_COUNT before running the CTAS statement:

-- Set the maximum row count to 500,000.
SET ADB_CONFIG TEMP_TABLE_MAX_ROW_COUNT=500000;

Query a cache table

Query a cache table the same way you query a standard table:

SELECT * FROM caches.c1;

List all cache tables

To see all cache tables in the cluster and their status, query the INFORMATION_SCHEMA.KEPLER_CACHE_TABLE_STATUS_MERGED system view:

SELECT * FROM INFORMATION_SCHEMA.KEPLER_CACHE_TABLE_STATUS_MERGED;

Usage notes

Read-only after creation: Cache tables cannot be modified after creation. Drop and recreate the cache table if you need different data.
Automatic deletion: A cache table is automatically deleted once it is not accessed for the duration set by TEMP_TABLE_EXPIRATION_TIME.
External databases not supported: Cache tables cannot be created in external databases.

Parameters

Parameter	Description	Default	Unit
`TEMP_TABLE_EXPIRATION_TIME`	Expiration period of a cache table. The table is automatically deleted if not accessed within this period.	`3600`	Seconds
`TEMP_TABLE_MAX_ROW_COUNT`	Maximum number of rows allowed in a cache table.	`300000`	Rows

Set parameters using the following syntax:

SET ADB_CONFIG TEMP_TABLE_EXPIRATION_TIME=3600;
SET ADB_CONFIG TEMP_TABLE_MAX_ROW_COUNT=300000;