Let Your Database Recommend the Indexes: Smarter Tuning in TiDB

Index tuning has always been one of the most effective ways to speed up database queries—but it’s also one of the most time-consuming and complex. When we first introduced the TiDB Index Advisor as a command-line tool in a previous blog, our goal was to simplify index selection for real-world workloads.

Now, with TiDB 8.5, we’re taking that idea even further. The new RECOMMEND INDEX SQL command brings intelligent, cost-based index suggestions directly into your workflow—no scripts, no guesswork, and no extra tooling required.

It’s all part of TiDB’s mission to make powerful database optimization accessible to everyone, whether you’re fine-tuning a single query or improving performance across a production workload.

Why Index Tuning Is Still So Hard

Indexes can make or break query performance. But choosing the right ones is more art than science—and often a slow, frustrating one at that.

Developers and DBAs face a familiar set of challenges:

• It’s a balancing act. Every index improves some queries but adds maintenance overhead.
• It requires deep expertise. Knowing which columns to index means understanding query plans and optimizer behavior.
• It doesn’t scale. Tuning one query is hard enough. Tuning hundreds or thousands? That’s a full-time job.
• It takes time. The cycle of trial, error, and benchmarking can stretch from days to weeks.

The TiDB Index Advisor tackles these pain points head-on. It analyzes real workloads—whether OLTP, OLAP, or hybrid—and recommends indexes that deliver measurable impact. And with TiDB 8.5, that intelligence is now just a SQL command away.

Meet the RECOMMEND INDEX Command

With TiDB 8.5, index recommendations are now built directly into SQL. The new RECOMMEND INDEX command lets you analyze individual queries or entire workloads with a single line—no external tools or scripts needed.

At its core, the syntax is simple:

RECOMMEND INDEX [RUN | FOR <SQL> | <OPTIONS> ];

You can use it in two main ways:

1. Recommend Indexes for a Single Query

Use the FOR option to get index suggestions for a specific SQL statement.

RECOMMEND INDEX FOR "SELECT a, b FROM t WHERE a = 1 AND b = 1";

2. Recommend Indexes for Your Full Workload

Use the RUN option to analyze top queries from the built-in statement summary table.

RECOMMEND INDEX RUN;

Both modes return structured results that include the recommended index, affected table, improvement score, and the exact CREATE INDEX statement to apply. It’s a fast, repeatable way to see which indexes will have the biggest performance impact.

Example: Recommending an Index for a Single Query

Let’s say you’re working with a simple table with 2,000 rows—enough to trigger full scans if no indexes are present:

CREATE TABLE t(a INT, b INT, c INT);

For simplicity, we’ll skip the insert statements here, but assume the table has been populated with representative sample data. You now want to improve the performance of this query:

SELECT a, b FROM t WHERE a = 1 AND b = 1;

Run the RECOMMEND INDEX command:

RECOMMEND INDEX FOR "SELECT a, b FROM t WHERE a = 1 AND b = 1";

Here’s a sample output:

+----------+-------+------------+---------------+------------+---------------------------------------------------------------+----------------------------------------------------------+-------------------------------+
| database | table | index_name | index_columns | index_size | reason                                                        | top_impacted_query                                       | create_index_statement         |
+----------+-------+------------+---------------+------------+---------------------------------------------------------------+----------------------------------------------------------+-------------------------------+
| test     | t     | idx_a_b    | a, b          | 19872      | Columns a and b appear in equality filters                    | SELECT a, b FROM t WHERE a = 1 AND b = 1 (improvement: ~99.99%) | CREATE INDEX idx_a_b ON t(a, b); |
+----------+-------+------------+---------------+------------+---------------------------------------------------------------+----------------------------------------------------------+-------------------------------+

Instead of suggesting separate single-column indexes on a and b, the advisor identifies that a combined index on (a, b) offers the best performance. Under the hood, it evaluates multiple hypothetical index combinations and picks the one with the lowest cost.

Here’s the difference in the execution plan:

Without index (full table scan):

EXPLAIN FORMAT='verbose' SELECT a, b FROM t WHERE a = 1 AND b = 1;

+---------------------+---------+------------+-----------+---------------+----------------------------------+
| id                  | estRows | estCost    | task      | access object | operator info                    |
+---------------------+---------+------------+-----------+---------------+----------------------------------+
| TableReader_7       | 0.01    | 196066.71  | root      |               | data:Selection_6                 |
| └─Selection_6       | 0.01    | 2941000.00 | cop[tikv] |               | eq(test.t.a, 1), eq(test.t.b, 1) |
|   └─TableFullScan_5 | 5000.00 | 2442000.00 | cop[tikv] | table:t       | keep order:false, stats:pseudo   |
+---------------------+---------+------------+-----------+---------------+----------------------------------+

With hypothetical index (idx_a_b):

EXPLAIN FORMAT='verbose'
SELECT /*+ HYPO_INDEX(t, idx_a_b, a, b) */ a, b FROM t WHERE a = 1 AND b = 1;

+---------------------+---------+---------+-----------+-----------------------------+-------------------------------------------------+
| id                  | estRows | estCost | task      | access object               | operator info                                   |
+---------------------+---------+---------+-----------+-----------------------------+-------------------------------------------------+
| IndexReader_6       | 0.05    | 1.10    | root      |                             | index:IndexRangeScan_5                          |
| └─IndexRangeScan_5  | 0.05    | 10.18   | cop[tikv] | table:t, index:idx_a_b(a,b) | range:[1 1,1 1], keep order:false, stats:pseudo |
+---------------------+---------+---------+-----------+-----------------------------+-------------------------------------------------+

Note: In addition to powering the advisor behind the scenes, Hypothetical Indexes are also available as a standalone feature. You can use them directly to simulate the impact of an index before creating it, as shown above using the HYPO_INDEX hint.

What’s the benefit?

Without an index, TiDB must scan the entire table, evaluate every row, and filter on a and b. This is expensive, especially as data grows.

With the recommended index, TiDB jumps straight to the matching rows using a fast index range scan. That means orders-of-magnitude lower cost, less CPU and I/O, and faster response times—all automatically suggested by the advisor.

Example: Recommending Indexes for a Full Workload

In real-world environments, performance issues rarely come from just one query. TiDB’s Index Advisor can evaluate your entire workload and suggest indexes that have the biggest impact across the board.

Suppose you’re running queries against two tables (t1 and t2), each with around 5,000 rows. Your workload includes:

SELECT a, b FROM t1 WHERE a = 1 AND b <= 5;
SELECT d FROM t1 ORDER BY d LIMIT 10;
SELECT * FROM t1 JOIN t2 ON t1.d = t2.d WHERE t1.a = 1;

Running this simple command:

RECOMMEND INDEX RUN;

Produces results like this:

+----------+-------+------------+---------------+------------+-------------------------------------------------------------+--------------------------------------------------------------+----------------------------------+
| database | table | index_name | index_columns | index_size | reason                                                      | top_impacted_query                                           | create_index_statement           |
+----------+-------+------------+---------------+------------+-------------------------------------------------------------+--------------------------------------------------------------+----------------------------------+
| test     | t1    | idx_a_b    | a, b          | 19872      | Columns a and b appear in equality or range filters         | SELECT a, b FROM t1 WHERE a = 1 AND b <= 5                   | CREATE INDEX idx_a_b ON t1(a,b); |
| test     | t1    | idx_d      | d             | 9936       | Column d used in ORDER BY and join predicate                | SELECT d FROM t1 ORDER BY d LIMIT 10                         | CREATE INDEX idx_d ON t1(d);     |
| test     | t2    | idx_d      | d             | 9936       | Column d used in join condition with t1.d                   | SELECT * FROM t1 JOIN t2 ON t1.d = t2.d WHERE t1.a = 1       | CREATE INDEX idx_d ON t2(d);     |
+----------+-------+------------+---------------+------------+-------------------------------------------------------------+--------------------------------------------------------------+----------------------------------+

What’s happening behind the scenes?

• TiDB pulls real SQL statements from information_schema.statements_summary.
• It ranks them by frequency—because often-run queries matter most.
• By default, it analyzes the top 1,000 queries (configurable with the max_num_query option).
• It runs cost-based analysis to suggest high-impact indexes, not just obvious ones.

This workload-aware approach means your index recommendations are based on actual query behavior, not guesswork or one-off tuning. Whether your workload is OLTP, OLAP, or hybrid, the advisor helps you boost overall performance with minimal effort.

Viewing and Persisting Recommendations

Every time you run RECOMMEND INDEX, TiDB logs the results in a system table called mysql.index_advisor_results so you can review, audit, or automate them later.

You can query it like this:

SELECT * FROM mysql.index_advisor_results;

Example output:

+----+---------------------+---------------------+-------------+------------+------------+---------------+---------------------------------------------------------+------------------------------------------------------------+-----------------------------+-------+
| id | created_at          | updated_at          | schema_name | table_name | index_name | index_columns | index_details                                           | top_impacted_queries                                        | workload_impact             | extra |
+----+---------------------+---------------------+-------------+------------+------------+---------------+---------------------------------------------------------+------------------------------------------------------------+-----------------------------+-------+
|  1 | 2024-12-10 11:44:45 | 2024-12-10 11:44:45 | test        | t1         | idx_a_b    | a, b          | Columns a, b used in WHERE clause                       | SELECT a, b FROM t1 WHERE a = 1 AND b <= 5                 | ~39% overall improvement     | NULL  |
|  2 | 2024-12-10 11:44:45 | 2024-12-10 11:44:45 | test        | t1         | idx_d      | d             | Column d used in ORDER BY and join                      | SELECT d FROM t1 ORDER BY d LIMIT 10                       | ~22% overall improvement     | NULL  |
|  3 | 2024-12-10 11:44:45 | 2024-12-10 11:44:45 | test        | t2         | idx_d      | d             | Column d used in join with t1                           | SELECT * FROM t1 JOIN t2 ON t1.d = t2.d WHERE t1.a = 1     | ~36% overall improvement     | NULL  |
+----+---------------------+---------------------+-------------+------------+------------+---------------+---------------------------------------------------------+------------------------------------------------------------+-----------------------------+-------+

This system table gives you:

• A persistent history of recommendations
• Estimated impact for individual queries and overall workloads
• Ready-to-apply CREATE INDEX statements

It’s especially helpful for staging environments, CI pipelines, or teams that want to track recommendations before rollout.

Fine-Tuning the Advisor with SET and SHOW

TiDB gives you control over how RECOMMEND INDEX works. You can adjust limits, timeouts, and index constraints to match your workload and environment.

To view the current settings:

RECOMMEND INDEX SHOW;

Example output:

+-------------------+--------+----------------------------------------------------------+
| option            | value  | description                                              |
+-------------------+--------+----------------------------------------------------------+
| max_num_index     | 5      | Maximum number of indexes to recommend                   |
| max_index_columns | 3      | Maximum number of columns in a multi-column index        |
| max_num_query     | 1000   | Maximum number of queries to analyze from the workload   |
| timeout           | 30s    | Max time to run the advisor                              |
+-------------------+--------+----------------------------------------------------------+

To update a setting, use the SET syntax:

RECOMMEND INDEX SET timeout = '20s';

These options help you:

• Limit the number of suggestions to fit operational constraints
• Prevent overly complex indexes
• Focus on a shorter or longer query history
• Control runtime to avoid long advisor executions in production

By fine-tuning these parameters, you can shape the advisor’s behavior to balance performance, safety, and usability in your specific environment.

How TiDB Compares to Other Index Tuning Advisors

Index recommendation isn’t new—but how it’s done makes all the difference.

Most commercial databases offer index tuning tools. Some are rule-based, while others require external scripts or GUIs and others analyze one query at a time. And most are locked behind proprietary licensing.

TiDB takes a different approach:

• Open source and fully integrated
• Cost-based, not just rule-based
• Workload-aware—it looks at your most frequent and most expensive queries, not just one at a time
• SQL-native, so it fits naturally into automation and developer workflows

Here’s how TiDB compares to other popular databases:

	Open Source	SQL-Based Index Recs	Analyzes Many Queries at Once	Suggests Indexes Automatically	How It Chooses Indexes
TiDB	Yes	Yes	Yes	Yes	Runs simulations to find lowest-cost indexes (Cost-based strategy)
PostgreSQL (hypopg)	Yes	Partial (requires extension)	No	No	User compares simulated plans manually (Hypothetical indexing)
MySQL	Yes	No	No	Limited	Uses fixed query patterns (Rule-based strategy)
SQL Server	No	GUI only (not available via SQL)	Yes	Yes	Estimates plan cost for each index option (Cost-based strategy)
Oracle	No	GUI only (not available via SQL)	Yes	Yes	Uses optimizer cost models (Cost-based strategy)
CockroachDB	No	No	No	No	User must manually decide and test (Manual indexing only)

Glossary: How These Index Tuning Strategies Work

• Cost-based: Simulates multiple query plans to choose the most efficient index
• Hypothetical: Lets users test imagined indexes, but leaves decision-making to them
• Rule-based: Suggests indexes based on simple heuristics (e.g., column in WHERE clause)
• Manual: No built-in tooling—users must define, test, and tune indexes themselves
• Workload-aware: The advisor analyzes many queries, prioritizing those that are most frequent or resource-intensive, to deliver the biggest total performance gains

Index Tuning Benchmarks and Real-World Results

TiDB’s Index Advisor isn’t just smart in theory—it delivers measurable performance improvements across a range of workloads, from synthetic benchmarks to real customer deployments.

Benchmark Highlights

• TPC-H (OLAP benchmark): Even in analytical workloads where indexes often have limited benefit, TiDB’s advisor delivered a 16% performance gain, optimizing selective filters and reducing scan overhead.
• Join Order Benchmark (JOB): This mixed workload includes complex joins, nested subqueries, and varying data distributions. The advisor improved overall query performance by 46%, showing strength in both OLTP and OLAP patterns.
• Web3bench (HTAP benchmark): In a hybrid transactional/analytical benchmark developed by TiDB and published at the TPC-TC conference, the advisor achieved a 75% performance improvement, proving its value in real-time data platforms.

Customer Simulations

• Global Home-Sharing Marketplace: In an internal simulation of 8 real production queries, the Index Advisor improved performance by 95%, dramatically reducing latency on heavy join and filter operations.
• Web3 Customer: Before the advisor, 50% of their most important queries triggered out-of-memory (OOM) errors. Manual indexing took weeks and introduced redundancy (20 indexes). The advisor solved the same problems with just 10 indexes and a 5% higher success rate.

Together, these results show that TiDB’s Index Advisor isn’t just accurate—it’s practical, scalable, and often better than manual tuning.

Get Started with RECOMMEND INDEX

You can start using TiDB’s Index Advisor in just a few steps—no setup, no plugins, and no code changes.

If you’re tuning a specific query:

RECOMMEND INDEX FOR "SELECT * FROM my_table WHERE user_id = 42";

If you want to analyze your most frequent queries:

RECOMMEND INDEX RUN;

By default, TiDB looks at the top 1,000 queries in statements_summary and recommends the most impactful indexes across your workload. You can control this behavior with options like timeout, max_num_query, and max_index_columns:

RECOMMEND INDEX SET timeout = '20s';
RECOMMEND INDEX SHOW;

All results are stored in mysql.index_advisor_results, so you can review or script index creation later.

From Hours to Seconds: A Better Path to Index Tuning

The TiDB Index Advisor brings intelligent, cost-based indexing directly into your workflow. Whether you’re optimizing a single query or improving performance across thousands, RECOMMEND INDEX helps you get better results, faster.

With native SQL integration, full workload awareness, and proven results across real-world use cases, it’s one of the simplest ways to unlock performance in TiDB—no trial-and-error, no manual tuning loops.

To learn more, check out the documentation or try it out for yourself in a test environment.