Syntax of AnalyticDB for MySQL cache tables - AnalyticDB

AnalyticDB for MySQL provides cache tables to temporarily store query results to facilitate data search and management. This topic describes how to use cache tables.

Prerequisites

An AnalyticDB for MySQL cluster of V3.2.0 or later is created.

Note

To view and update the minor version of an AnalyticDB for MySQL cluster, log on to the AnalyticDB for MySQL console and go to the Configuration Information section of the Cluster Information page.

Overview

Cache tables work in a similar way to the temporary tables in traditional databases. Cache tables can temporarily store query results to help you analyze and search result set data. Cache tables store hot data in the storage space. AnalyticDB for MySQL automatically deletes the unused cache tables based on eviction policies.

Usage

Create a cache table

You can use only the CREATE TABLE AS SELECT (CTAS) statement to create a cache table and write data to the table. To create a cache table but not a standard table, you must specify ENGINE='CACHE' in the statement. After you create a cache table, you cannot modify the table.

Sort the data of the adb_demo.customer table based on the age column and write the data to the c1 cache table.

-- (Optional) Create a database named caches.
CREATE DATABASE IF NOT EXISTS caches;

-- Execute the following CTAS statement to create a cache table:
CREATE TABLE caches.c1 ENGINE='CACHE'
	AS SELECT * FROM adb_demo.customer ORDER BY age LIMIT 9999;

Note

We recommend that you create a database to manage cache tables. You can also use an existing database except external databases to create cache tables.

Query the data of a cache table

Query the data of the caches.c1 cache table.

SELECT * FROM caches.c1;

Query the data of existing cache tables

Query the data of all cache tables in the cluster.

SELECT * FROM INFORMATION_SCHEMA.KEPLER_CACHE_TABLE_STATUS_MERGED;

Parameter configuration

Configure the TEMP_TABLE_EXPIRATION_TIME parameter

This parameter specifies the expiration period of a cache table. Unit: seconds. Default value: 3600. If a cache table is not accessed within 1 hour, the table is automatically deleted.

SET ADB_CONFIG TEMP_TABLE_EXPIRATION_TIME=3600;

Configure the TEMP_TABLE_MAX_ROW_COUNT parameter

This parameter specifies the maximum number of rows that are allowed in a cache table. Default value: 300000.

SET ADB_CONFIG TEMP_TABLE_MAX_ROW_COUNT=300000;