Documentation Index
Fetch the complete documentation index at: https://mintlify.com/pingcap/tidb/llms.txt
Use this file to discover all available pages before exploring further.
TiDB’s cost-based optimizer (CBO) automatically selects execution plans, but understanding how to read plans and guide the optimizer is essential for tuning queries in production.
Reading execution plans with EXPLAIN
EXPLAIN shows the operator tree TiDB will use to execute a query without actually running it.
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
+-------------------------------+----------+-----------+---------------+--------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------+----------+-----------+---------------+--------------------------------+
| IndexLookUp_10 | 10.00 | root | | |
| ├─IndexRangeScan_8(Build) | 10.00 | cop[tikv] | table:orders, | range:[42,42], keep order:false|
| | | | index:idx_cid | |
| └─TableRowIDScan_9(Probe) | 10.00 | cop[tikv] | table:orders | keep order:false |
+-------------------------------+----------+-----------+---------------+--------------------------------+
FORMAT='brief' produces a compact output that is easier to read for large plans:
EXPLAIN FORMAT='brief' SELECT * FROM orders WHERE customer_id = 42;
+-----------------+----------+------+-----------+--------------------------------+
| id | estRows | task | access | operator info |
+-----------------+----------+------+-----------+--------------------------------+
| IndexLookUp | 10.00 | root | | |
| ├─IndexRange | 10.00 | cop | idx_cid | range:[42,42] |
| └─TableRowID | 10.00 | cop | orders | |
+-----------------+----------+------+-----------+--------------------------------+
EXPLAIN ANALYZE
EXPLAIN ANALYZE runs the query and augments the plan with actual runtime statistics, including row counts and execution time:
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
| Column | Description |
|---|
actRows | Actual number of rows processed |
execution info | Time spent in each operator |
memory | Memory consumed by the operator |
disk | Spill-to-disk volume |
estRows and actRows indicates stale statistics — run ANALYZE TABLE to update them.
Common operators
| Operator | Description |
|---|
TableFullScan | Full table scan; no usable index |
TableRangeScan | Scan a range of the primary key |
IndexFullScan | Full scan of an index |
IndexRangeScan | Scan a range within an index |
IndexLookUp | Index scan followed by a row fetch (two-phase) |
HashJoin | Hash-based join; builds a hash table on the smaller side |
MergeJoin | Sort-merge join; requires sorted input on join keys |
HashAgg | Hash-based aggregation |
StreamAgg | Streaming aggregation on pre-sorted input |
Projection | Evaluates expressions and selects columns |
Selection | Filters rows by a predicate |
Index hints
Index hints override the optimizer’s index selection for a specific query.
-- Prefer a specific index
SELECT * FROM orders USE INDEX (idx_status) WHERE status = 'pending';
-- Force a specific index (error if index cannot be used)
SELECT * FROM orders FORCE INDEX (idx_status) WHERE status = 'pending';
-- Exclude an index from consideration
SELECT * FROM orders IGNORE INDEX (idx_status) WHERE status = 'pending';
FORCE INDEX causes the query to fail rather than fall back to a full table scan if the index is unusable. Use USE INDEX when you want a preference without a hard requirement.
Optimizer hints
Optimizer hints go inside /*+ ... */ comments immediately after the SELECT, UPDATE, or DELETE keyword.
Join hints
-- Force a hash join between t1 and t2
SELECT /*+ HASH_JOIN(t1, t2) */ *
FROM orders t1
JOIN customers t2 ON t1.customer_id = t2.id;
-- Force a merge join
SELECT /*+ MERGE_JOIN(t1, t2) */ *
FROM orders t1
JOIN customers t2 ON t1.customer_id = t2.id;
-- Force an index nested-loop join
SELECT /*+ INL_JOIN(t2) */ *
FROM orders t1
JOIN customers t2 ON t1.customer_id = t2.id;
Index hints
-- Hint the optimizer to use a specific index
SELECT /*+ USE_INDEX(orders, idx_status) */ *
FROM orders
WHERE status = 'pending';
-- Ignore an index
SELECT /*+ IGNORE_INDEX(orders, idx_status) */ *
FROM orders
WHERE status = 'pending';
Other common hints
-- Read from a follower replica (for read-heavy workloads)
SELECT /*+ READ_FROM_STORAGE(TIFLASH[t]) */ count(*) FROM large_table t;
-- Set the maximum execution time for this query (ms)
SELECT /*+ MAX_EXECUTION_TIME(5000) */ * FROM orders;
Updating statistics
TiDB’s optimizer relies on table statistics to estimate row counts. Stale statistics are one of the most common causes of poor plan choices.
-- Update statistics for a table
ANALYZE TABLE orders;
-- Update statistics for specific columns and indexes
ANALYZE TABLE orders COLUMNS customer_id, status INDEX idx_status;
TiDB runs auto-analyze in the background when a table’s data changes significantly. You can configure the auto-analyze threshold with the tidb_auto_analyze_ratio system variable.
SELECT table_name, last_analyzed_at, row_count
FROM information_schema.tidb_table_stats
WHERE table_schema = 'mydb';
Slow query log
TiDB records queries exceeding tidb_slow_log_threshold (default 300 ms) to the slow query log and the INFORMATION_SCHEMA.SLOW_QUERY table.
-- Find the slowest recent queries
SELECT query, query_time, parse_time, compile_time, process_time
FROM information_schema.slow_query
ORDER BY query_time DESC
LIMIT 10;
-- Filter by table
SELECT query, query_time, index_names
FROM information_schema.slow_query
WHERE query LIKE '%orders%'
ORDER BY query_time DESC
LIMIT 20;
Common optimization patterns
Use covering indexes
A covering index includes all columns referenced in the query, eliminating the second lookup phase (IndexLookUp → IndexFullScan):
-- Without covering index: IndexLookUp (two phases)
SELECT status, created_at FROM orders WHERE customer_id = 42;
-- Create a covering index
CREATE INDEX idx_cid_cover ON orders (customer_id, status, created_at);
-- Now: IndexRangeScan only (one phase)
SELECT status, created_at FROM orders WHERE customer_id = 42;
Avoid full table scans
A TableFullScan on a large table is almost always a performance problem. Common causes:
- No index on the filtered column.
- Filtering on a function applied to an indexed column:
WHERE YEAR(created_at) = 2024 prevents index use; rewrite as WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'.
- Leading column of a composite index not included in the
WHERE clause.
Choose the right join algorithm
| Scenario | Recommended join |
|---|
| One small table, one large table | HASH_JOIN or INL_JOIN |
| Both tables sorted on join key | MERGE_JOIN |
| High-cardinality equi-join | HASH_JOIN |
| Index available on join key | INL_JOIN |
Prefer TIFLASH for analytical queries
For large aggregations and scans, push computation to TiFlash:
SELECT /*+ READ_FROM_STORAGE(TIFLASH[orders]) */
status, SUM(amount), COUNT(*)
FROM orders
GROUP BY status;
