Statement Cache

The statement cache stores execution plans for compiled SQL statements. This speeds up repeated execution of the same query.

Prepared Statement Cache

-- Prepared statement (similar to MySQL)
PREPARE stmt FROM 'SELECT * FROM users WHERE id = ?';
SET @id = 1;
EXECUTE stmt USING @id;
DEALLOCATE PREPARE stmt;

TiDB Statement Plan Cache

TiDB automatically stores execution plans for prepared statements:

-- Enable the statement plan cache
SET GLOBAL tidb_enable_prepared_plan_cache = ON;

-- Cache size (default 100)
SET GLOBAL tidb_prepared_plan_cache_size = 100;

Non-Prepared Statement Cache

Since TiDB v7.0, non-prepared statements can also use the plan cache:

-- Enable the non-prepared statement cache
SET GLOBAL tidb_enable_non_prepared_plan_cache = ON;

-- This query will use the cache after its first execution
SELECT * FROM users WHERE status = 'active' AND created_at > '2024-01-01';
-- Subsequent executions are faster

Instance-Level Plan Cache

An experimental feature for sharing the cache at the instance level:

-- Enable the instance plan cache (experimental)
SET GLOBAL tidb_enable_instance_plan_cache = ON;

Usage Examples

High-Frequency Query

-- A query executed thousands of times per second
SELECT * FROM products WHERE category_id = ? AND status = 'active';

-- With the plan cache, the execution plan is computed only once
-- Subsequent executions use the cached plan directly

OLTP Workload

-- For an e-commerce application with many INSERT queries
PREPARE insert_order FROM
  'INSERT INTO orders (user_id, total, status) VALUES (?, ?, ?)';

-- Execute repeatedly with different parameters
EXECUTE insert_order USING 1, 250000, 'pending';
EXECUTE insert_order USING 2, 180000, 'pending';

Monitoring the Cache

-- Show plan cache statistics
SELECT * FROM information_schema.TIDB_TRX_SUMMARY;

-- Check whether the plan cache is used
EXPLAIN FORMAT='brief' SELECT * FROM users WHERE id = 1;

Limitations

The plan cache is not used when:
The query uses a nondeterministic function
The query uses LOAD DATA
The statement is a DDL
The query is too complex

In the next chapter, we will look at SQL Plan Management in TiDB.