Query Result Cache

TiDB Cloud Lake caches and persists the query results for every executed query when enabled. This can be used to great effect to dramatically reduce the time it takes to get an answer.

Cache Usage Conditions

Query results are reused from cache only when all conditions are satisfied:

Quick Start

Enable query result caching in your session:

-- Enable query result cache
SET enable_query_result_cache = 1;

-- Optional: Cache all queries (including fast ones)
SET query_result_cache_min_execute_secs = 0;

Configuration Settings

Performance Example

This example demonstrates caching a TPC-H Q1 query:

1. Enable Caching

SET enable_query_result_cache = 1;
SET query_result_cache_min_execute_secs = 0;

2. First Execution (No Cache)

SELECT
    l_returnflag,
    l_linestatus,
    sum(l_quantity) as sum_qty,
    sum(l_extendedprice) as sum_base_price,
    sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
    sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
    avg(l_quantity) as avg_qty,
    avg(l_extendedprice) as avg_price,
    avg(l_discount) as avg_disc,
    count(*) as count_order
FROM lineitem
WHERE l_shipdate <= add_days(to_date('1998-12-01'), -90)
GROUP BY l_returnflag, l_linestatus
ORDER BY l_returnflag, l_linestatus;

Result: 4 rows in 21.492 seconds (600M rows processed)

3. Verify Cache Entry

SELECT sql, query_id, result_size, num_rows FROM system.query_cache;

4. Second Execution (From Cache)

Run the same query again.

Result: 4 rows in 0.164 seconds (0 rows processed)

Cache Management

Monitor Cache Usage

SELECT * FROM system.query_cache;

Access Cached Results

SELECT * FROM RESULT_SCAN(LAST_QUERY_ID());

Cache Lifecycle

Cached results are automatically removed when:

• TTL expires (default: 5 minutes)
• Result size exceeds limit (default: 1MB)
• Session ends (cache is session-scoped)
• Underlying data changes (automatic invalidation for consistency)
• Table structure changes (schema modifications invalidate cache)