{"id":27341,"date":"2025-05-15T07:49:39","date_gmt":"2025-05-15T14:49:39","guid":{"rendered":"https:\/\/www.pingcap.com\/?p=27341"},"modified":"2025-05-15T07:49:40","modified_gmt":"2025-05-15T14:49:40","slug":"let-your-database-recommend-indexes-index-tuning-tidb","status":"publish","type":"post","link":"https:\/\/www.pingcap.com\/ko\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/","title":{"rendered":"Let Your Database Recommend the Indexes: Smarter Tuning in TiDB"},"content":{"rendered":"<p>Index tuning has always been one of the most effective ways to speed up database queries\u2014but it\u2019s also one of the most time-consuming and complex. When we first introduced the TiDB Index Advisor as a command-line tool in a <a href=\"https:\/\/www.pingcap.com\/ko\/blog\/introducing-tiadvisor-automated-tidb-index-discovery\/\">previous blog<\/a>, our goal was to simplify index selection for real-world workloads.<\/p>\n\n\n\n<p>Now, with <a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/release-8.5.0\/\">TiDB 8.5<\/a>, we\u2019re taking that idea even further. The new <code>RECOMMEND INDEX<\/code> SQL command brings intelligent, cost-based index suggestions directly into your workflow\u2014no scripts, no guesswork, and no extra tooling required.<\/p>\n\n\n\n<p>It\u2019s all part of TiDB\u2019s mission to make powerful database optimization accessible to everyone, whether you\u2019re fine-tuning a single query or improving performance across a production workload.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Why_Index_Tuning_Is_Still_So_Hard\"><\/span>Why Index Tuning Is Still So Hard<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Indexes can make or break query performance. But choosing the right ones is more art than science\u2014and often a slow, frustrating one at that.<\/p>\n\n\n\n<p>Developers and DBAs face a familiar set of challenges:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>It\u2019s a balancing act.<\/strong> Every index improves some queries but adds maintenance overhead.<\/li>\n\n\n\n<li><strong>It requires deep expertise.<\/strong> Knowing which columns to index means understanding query plans and optimizer behavior.<\/li>\n\n\n\n<li><strong>It doesn\u2019t scale.<\/strong> Tuning one query is hard enough. Tuning hundreds or thousands? That\u2019s a full-time job.<\/li>\n\n\n\n<li><strong>It takes time.<\/strong> The cycle of trial, error, and benchmarking can stretch from days to weeks.<\/li>\n<\/ul>\n\n\n\n<p>The TiDB Index Advisor tackles these pain points head-on. It analyzes real workloads\u2014whether OLTP, OLAP, or hybrid\u2014and recommends indexes that deliver measurable impact. And with TiDB 8.5, that intelligence is now just a SQL command away.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Meet_the_RECOMMEND_INDEX_Command\"><\/span>Meet the <code>RECOMMEND INDEX<\/code> Command<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>With TiDB 8.5, index recommendations are now built directly into SQL. The new <a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/index-advisor\/#recommend-indexes-using-the-recommend-index-statement\"><code>RECOMMEND INDEX<\/code> command<\/a> lets you analyze individual queries or entire workloads with a single line\u2014no external tools or scripts needed.<\/p>\n\n\n\n<p>At its core, the syntax is simple:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>RECOMMEND INDEX &#91;RUN | FOR &lt;SQL&gt; | &lt;OPTIONS&gt; ];<\/code><\/pre>\n\n\n\n<p>You can use it in two main ways:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">1. <strong>Recommend Indexes for a Single Query<\/strong><\/h3>\n\n\n\n<p>Use the <code>FOR<\/code> option to get index suggestions for a specific SQL statement.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>RECOMMEND INDEX FOR \"SELECT a, b FROM t WHERE a = 1 AND b = 1\";<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\"><strong>2<\/strong>. <strong>Recommend Indexes for Your Full Workload<\/strong><\/h3>\n\n\n\n<p>Use the <code>RUN<\/code> option to analyze top queries from the built-in statement summary table.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>RECOMMEND INDEX RUN;<\/code><\/pre>\n\n\n\n<p>Both modes return structured results that include the recommended index, affected table, improvement score, and the exact <code>CREATE INDEX<\/code> statement to apply. It\u2019s a fast, repeatable way to see which indexes will have the biggest performance impact.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Example: Recommending an Index for a Single Query<\/h3>\n\n\n\n<p>Let\u2019s say you\u2019re working with a simple table with 2,000 rows\u2014enough to trigger full scans if no indexes are present:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLE t(a INT, b INT, c INT);<\/code><\/pre>\n\n\n\n<p>For simplicity, we\u2019ll 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:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT a, b FROM t WHERE a = 1 AND b = 1;<\/code><\/pre>\n\n\n\n<p>Run the <code>RECOMMEND INDEX<\/code> command:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>RECOMMEND INDEX FOR \"SELECT a, b FROM t WHERE a = 1 AND b = 1\";<\/code><\/pre>\n\n\n\n<p>Here\u2019s a sample output:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>+----------+-------+------------+---------------+------------+---------------------------------------------------------------+----------------------------------------------------------+-------------------------------+\n| database | table | index_name | index_columns | index_size | reason                                                        | top_impacted_query                                       | create_index_statement         |\n+----------+-------+------------+---------------+------------+---------------------------------------------------------------+----------------------------------------------------------+-------------------------------+\n| 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); |\n+----------+-------+------------+---------------+------------+---------------------------------------------------------------+----------------------------------------------------------+-------------------------------+<\/code><\/pre>\n\n\n\n<p>Instead of suggesting separate single-column indexes on a and b, the advisor identifies that a <strong>combined index on (a, b)<\/strong> offers the best performance. Under the hood, it evaluates multiple hypothetical index combinations and picks the one with the lowest cost.<\/p>\n\n\n\n<p>Here\u2019s the difference in the execution plan:<\/p>\n\n\n\n<p><strong>Without index (full table scan):<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>EXPLAIN FORMAT='verbose' SELECT a, b FROM t WHERE a = 1 AND b = 1;\n\n+---------------------+---------+------------+-----------+---------------+----------------------------------+\n| id                  | estRows | estCost    | task      | access object | operator info                    |\n+---------------------+---------+------------+-----------+---------------+----------------------------------+\n| TableReader_7       | 0.01    | 196066.71  | root      |               | data:Selection_6                 |\n| \u2514\u2500Selection_6       | 0.01    | 2941000.00 | cop&#91;tikv] |               | eq(test.t.a, 1), eq(test.t.b, 1) |\n|   \u2514\u2500TableFullScan_5 | 5000.00 | 2442000.00 | cop&#91;tikv] | table:t       | keep order:false, stats:pseudo   |\n+---------------------+---------+------------+-----------+---------------+----------------------------------+<\/code><\/pre>\n\n\n\n<p><strong>With hypothetical index (idx_a_b):<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>EXPLAIN FORMAT='verbose'\nSELECT \/*+ HYPO_INDEX(t, idx_a_b, a, b) *\/ a, b FROM t WHERE a = 1 AND b = 1;\n\n+---------------------+---------+---------+-----------+-----------------------------+-------------------------------------------------+\n| id                  | estRows | estCost | task      | access object               | operator info                                   |\n+---------------------+---------+---------+-----------+-----------------------------+-------------------------------------------------+\n| IndexReader_6       | 0.05    | 1.10    | root      |                             | index:IndexRangeScan_5                          |\n| \u2514\u2500IndexRangeScan_5  | 0.05    | 10.18   | cop&#91;tikv] | table:t, index:idx_a_b(a,b) | range:&#91;1 1,1 1], keep order:false, stats:pseudo |\n+---------------------+---------+---------+-----------+-----------------------------+-------------------------------------------------+<\/code><\/pre>\n\n\n\n<p><strong>Note:<\/strong> In addition to powering the advisor behind the scenes, <a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/index-advisor\/#hypothetical-indexes\">Hypothetical Indexes<\/a> 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.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>What\u2019s the benefit?<\/strong><\/h4>\n\n\n\n<p>Without an index, <a href=\"https:\/\/www.pingcap.com\/ko\/tidb-self-managed\/\">\ud2f0DB<\/a> must scan the entire table, evaluate every row, and filter on a and b. This is expensive, especially as data grows.<\/p>\n\n\n\n<p>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\u2014all automatically suggested by the advisor.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Example: Recommending Indexes for a Full Workload<\/h3>\n\n\n\n<p>In real-world environments, performance issues rarely come from just one query. TiDB\u2019s Index Advisor can evaluate your entire workload and suggest indexes that have the biggest impact across the board.<\/p>\n\n\n\n<p>Suppose you\u2019re running queries against two tables (<code>t1<\/code> \uadf8\ub9ac\uace0 <code>t2<\/code>), each with around 5,000 rows. Your workload includes:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT a, b FROM t1 WHERE a = 1 AND b &lt;= 5;\nSELECT d FROM t1 ORDER BY d LIMIT 10;\nSELECT * FROM t1 JOIN t2 ON t1.d = t2.d WHERE t1.a = 1;<\/code><\/pre>\n\n\n\n<p>Running this simple command:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>RECOMMEND INDEX RUN;<\/code><\/pre>\n\n\n\n<p>Produces results like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>+----------+-------+------------+---------------+------------+-------------------------------------------------------------+--------------------------------------------------------------+----------------------------------+\n| database | table | index_name | index_columns | index_size | reason                                                      | top_impacted_query                                           | create_index_statement           |\n+----------+-------+------------+---------------+------------+-------------------------------------------------------------+--------------------------------------------------------------+----------------------------------+\n| 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 &lt;= 5                   | CREATE INDEX idx_a_b ON t1(a,b); |\n| 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);     |\n| 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);     |\n+----------+-------+------------+---------------+------------+-------------------------------------------------------------+--------------------------------------------------------------+----------------------------------+<\/code><\/pre>\n\n\n\n<p>What\u2019s happening behind the scenes?<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>TiDB pulls real SQL statements from <code>information_schema.statements_summary<\/code>.<\/li>\n\n\n\n<li>It ranks them by frequency\u2014because often-run queries matter most.<\/li>\n\n\n\n<li>By default, it analyzes the <strong>top 1,000 queries<\/strong> (configurable with the max_num_query option).<\/li>\n\n\n\n<li>It runs cost-based analysis to suggest high-impact indexes, not just obvious ones.<\/li>\n<\/ul>\n\n\n\n<p>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.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Viewing and Persisting Recommendations<\/h3>\n\n\n\n<p>Every time you run <code>RECOMMEND INDEX<\/code>, TiDB logs the results in a system table called <code>mysql.index_advisor_results<\/code> so you can review, audit, or automate them later.<\/p>\n\n\n\n<p>You can query it like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM mysql.index_advisor_results;<\/code><\/pre>\n\n\n\n<p>Example output:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>+----+---------------------+---------------------+-------------+------------+------------+---------------+---------------------------------------------------------+------------------------------------------------------------+-----------------------------+-------+\n| id | created_at          | updated_at          | schema_name | table_name | index_name | index_columns | index_details                                           | top_impacted_queries                                        | workload_impact             | extra |\n+----+---------------------+---------------------+-------------+------------+------------+---------------+---------------------------------------------------------+------------------------------------------------------------+-----------------------------+-------+\n|  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 &lt;= 5                 | ~39% overall improvement     | NULL  |\n|  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  |\n|  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  |\n+----+---------------------+---------------------+-------------+------------+------------+---------------+---------------------------------------------------------+------------------------------------------------------------+-----------------------------+-------+<\/code><\/pre>\n\n\n\n<p>This system table gives you:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>A persistent history of recommendations<\/li>\n\n\n\n<li>Estimated impact for individual queries and overall workloads<\/li>\n\n\n\n<li>Ready-to-apply <code>CREATE INDEX<\/code> statements<\/li>\n<\/ul>\n\n\n\n<p>It\u2019s especially helpful for staging environments, CI pipelines, or teams that want to track recommendations before rollout.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Fine-Tuning the Advisor with <code>SET<\/code> \uadf8\ub9ac\uace0 <code>SHOW<\/code><\/h3>\n\n\n\n<p>TiDB gives you control over how <code>RECOMMEND INDEX<\/code> works. You can adjust limits, timeouts, and index constraints to match your workload and environment.<\/p>\n\n\n\n<p>To view the current settings:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>RECOMMEND INDEX SHOW;<\/code><\/pre>\n\n\n\n<p>Example output:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>+-------------------+--------+----------------------------------------------------------+\n| option            | value  | description                                              |\n+-------------------+--------+----------------------------------------------------------+\n| max_num_index     | 5      | Maximum number of indexes to recommend                   |\n| max_index_columns | 3      | Maximum number of columns in a multi-column index        |\n| max_num_query     | 1000   | Maximum number of queries to analyze from the workload   |\n| timeout           | 30s    | Max time to run the advisor                              |\n+-------------------+--------+----------------------------------------------------------+<\/code><\/pre>\n\n\n\n<p>To update a setting, use the <code>SET<\/code> syntax:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>RECOMMEND INDEX SET timeout = '20s';<\/code><\/pre>\n\n\n\n<p>These options help you:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Limit the number of suggestions to fit operational constraints<\/li>\n\n\n\n<li>Prevent overly complex indexes<\/li>\n\n\n\n<li>Focus on a shorter or longer query history<\/li>\n\n\n\n<li>Control runtime to avoid long advisor executions in production<\/li>\n<\/ul>\n\n\n\n<p>By fine-tuning these parameters, you can shape the advisor\u2019s behavior to balance performance, safety, and usability in your specific environment.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"How_TiDB_Compares_to_Other_Index_Tuning_Advisors\"><\/span>How TiDB Compares to Other Index Tuning Advisors<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Index recommendation isn\u2019t new\u2014but how it\u2019s done makes all the difference.<\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>TiDB takes a different approach:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Open source and fully integrated<\/li>\n\n\n\n<li>Cost-based, not just rule-based<\/li>\n\n\n\n<li>Workload-aware\u2014it looks at your most frequent and most expensive queries, not just one at a time<\/li>\n\n\n\n<li>SQL-native, so it fits naturally into automation and developer workflows<\/li>\n<\/ul>\n\n\n\n<p>Here\u2019s how TiDB compares to other popular databases:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td><\/td><td><strong>Open Source<\/strong><\/td><td><strong>SQL-Based Index Recs<\/strong><\/td><td><strong>Analyzes Many Queries at Once<\/strong><\/td><td><strong>Suggests Indexes Automatically<\/strong><\/td><td><strong>How It Chooses Indexes<\/strong><\/td><\/tr><tr><td><strong>\ud2f0DB<\/strong><\/td><td>Yes<\/td><td>Yes<\/td><td>Yes<\/td><td>Yes<\/td><td>Runs simulations to find lowest-cost indexes (Cost-based strategy)<\/td><\/tr><tr><td><strong>PostgreSQL (hypopg)<\/strong><\/td><td>Yes<\/td><td>Partial (requires extension)<\/td><td>No<\/td><td>No<\/td><td>User compares simulated plans manually (Hypothetical indexing)<\/td><\/tr><tr><td><strong>MySQL<\/strong><\/td><td>Yes<\/td><td>No<\/td><td>No<\/td><td>Limited<\/td><td>Uses fixed query patterns (Rule-based strategy)<\/td><\/tr><tr><td><strong>SQL Server<\/strong><\/td><td>No<\/td><td>GUI only (not available via SQL)<\/td><td>Yes<\/td><td>Yes<\/td><td>Estimates plan cost for each index option (Cost-based strategy)<\/td><\/tr><tr><td><strong>Oracle<\/strong><\/td><td>No<\/td><td>GUI only (not available via SQL)<\/td><td>Yes<\/td><td>Yes<\/td><td>Uses optimizer cost models (Cost-based strategy)<\/td><\/tr><tr><td><strong>CockroachDB<\/strong><\/td><td>No<\/td><td>No<\/td><td>No<\/td><td>No<\/td><td>User must manually decide and test (Manual indexing only)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Glossary: How These Index Tuning Strategies Work<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Cost-based<\/strong>: Simulates multiple query plans to choose the most efficient index<\/li>\n\n\n\n<li><strong>Hypothetical<\/strong>: Lets users test imagined indexes, but leaves decision-making to them<\/li>\n\n\n\n<li><strong>Rule-based<\/strong>: Suggests indexes based on simple heuristics (e.g., column in <code>WHERE<\/code> clause)<\/li>\n\n\n\n<li><strong>Manual<\/strong>: No built-in tooling\u2014users must define, test, and tune indexes themselves<\/li>\n\n\n\n<li><strong>Workload-aware<\/strong>: The advisor analyzes many queries, prioritizing those that are most frequent or resource-intensive, to deliver the biggest total performance gains<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Index_Tuning_Benchmarks_and_Real-World_Results\"><\/span>Index Tuning Benchmarks and Real-World Results<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>TiDB\u2019s Index Advisor isn\u2019t just smart in theory\u2014it delivers measurable performance improvements across a range of workloads, from synthetic benchmarks to real customer deployments.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Benchmark Highlights<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>TPC-H (<\/strong><strong>OLAP<\/strong><strong> benchmark): <\/strong>Even in analytical workloads where indexes often have limited benefit, TiDB\u2019s advisor delivered a <strong>16% performance gain<\/strong>, optimizing selective filters and reducing scan overhead.<\/li>\n\n\n\n<li><strong>Join Order Benchmark (JOB): <\/strong>This mixed workload includes complex joins, nested subqueries, and varying data distributions. The advisor improved overall query performance by <strong>46%<\/strong>, showing strength in both OLTP and OLAP patterns.<\/li>\n\n\n\n<li><strong>Web3bench (<\/strong><strong>HTAP<\/strong><strong> benchmark): <\/strong>In a hybrid transactional\/analytical benchmark developed by TiDB and published at the TPC-TC conference, the advisor achieved a <strong>75% performance improvement<\/strong>, proving its value in real-time data platforms.<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Customer Simulations<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Global<\/strong> <strong>Home-Sharing Marketplace: <\/strong>In an internal simulation of 8 real production queries, the Index Advisor improved performance by <strong>95%<\/strong>, dramatically reducing latency on heavy join and filter operations.<\/li>\n\n\n\n<li><strong>Web3<\/strong><strong> Customer: <\/strong>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 <strong>10 indexes<\/strong> and a <strong>5% higher success rate<\/strong>.<\/li>\n<\/ul>\n\n\n\n<p>Together, these results show that TiDB\u2019s Index Advisor isn\u2019t just accurate\u2014it\u2019s practical, scalable, and often better than manual tuning.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Get_Started_with_RECOMMEND_INDEX\"><\/span>Get Started with <code>RECOMMEND INDEX<\/code><span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>You can start using TiDB\u2019s Index Advisor in just a few steps\u2014no setup, no plugins, and no code changes.<\/p>\n\n\n\n<p><strong>If you\u2019re tuning a specific query:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>RECOMMEND INDEX FOR \"SELECT * FROM my_table WHERE user_id = 42\";<\/code><\/pre>\n\n\n\n<p><strong>If you want to analyze your most frequent queries:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>RECOMMEND INDEX RUN;<\/code><\/pre>\n\n\n\n<p>By default, TiDB looks at the top 1,000 queries in <code>statements_summary<\/code> and recommends the most impactful indexes across your workload. You can control this behavior with options like <code>timeout<\/code>, <code>max_num_query<\/code>, \uadf8\ub9ac\uace0 <code>max_index_columns<\/code>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>RECOMMEND INDEX SET timeout = '20s';\nRECOMMEND INDEX SHOW;<\/code><\/pre>\n\n\n\n<p>All results are stored in <code>mysql.index_advisor_results<\/code>, so you can review or script index creation later.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"From_Hours_to_Seconds_A_Better_Path_to_Index_Tuning\"><\/span>From Hours to Seconds: A Better Path to Index Tuning<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>The TiDB Index Advisor brings intelligent, cost-based indexing directly into your workflow. Whether you\u2019re optimizing a single query or improving performance across thousands, <code>RECOMMEND INDEX<\/code> helps you get better results, faster.<\/p>\n\n\n\n<p>With native SQL integration, full workload awareness, and proven results across real-world use cases, it\u2019s one of the simplest ways to unlock performance in TiDB\u2014no trial-and-error, no manual tuning loops.<\/p>\n\n\n\n<p>To learn more, <a href=\"https:\/\/docs.pingcap.com\/tidb\/stable\/index-advisor\/\">check out the documentation<\/a> \ub610\ub294 <a href=\"https:\/\/tidbcloud.com\/\">try it out for yourself in a test environment<\/a>.<\/p>","protected":false},"excerpt":{"rendered":"<p>Index tuning has always been one of the most effective ways to speed up database queries\u2014but it\u2019s 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, [&hellip;]<\/p>\n","protected":false},"author":256,"featured_media":27357,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"ub_ctt_via":"","footnotes":""},"categories":[6],"tags":[147,16,388,157,111],"class_list":["post-27341","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-engineering","tag-distributed-sql","tag-distributed-system","tag-index-optimization","tag-sql","tag-tidb"],"acf":[],"featured_image_src":"https:\/\/static.pingcap.com\/files\/2025\/05\/15073240\/tidb_feature_1800x600-1-1-scaled.png","author_info":{"display_name":"Ahmad Ghazal","author_link":"https:\/\/www.pingcap.com\/ko\/blog\/author\/aghazal\/"},"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.9 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Index Tuning: How TiDB Provides Smarter Recommendations<\/title>\n<meta name=\"description\" content=\"TiDB 8.5 takes index tuning even further by offering intelligent, cost-based index suggestions\u2014no extra tooling required.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.pingcap.com\/ko\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/\" \/>\n<meta property=\"og:locale\" content=\"ko_KR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Index Tuning: How TiDB Provides Smarter Recommendations\" \/>\n<meta property=\"og:description\" content=\"TiDB 8.5 takes index tuning even further by offering intelligent, cost-based index suggestions\u2014no extra tooling required.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.pingcap.com\/ko\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/\" \/>\n<meta property=\"og:site_name\" content=\"TiDB\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/facebook.com\/pingcap2015\" \/>\n<meta property=\"article:published_time\" content=\"2025-05-15T14:49:39+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-05-15T14:49:40+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/static.pingcap.com\/files\/2025\/05\/15073411\/tidb_1200x627-1.png\" \/>\n\t<meta property=\"og:image:width\" content=\"2400\" \/>\n\t<meta property=\"og:image:height\" content=\"1254\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Ahmad Ghazal\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:image\" content=\"https:\/\/static.pingcap.com\/files\/2025\/05\/15073429\/tidb_twitter_1600x900-1-scaled.png\" \/>\n<meta name=\"twitter:creator\" content=\"@PingCAP\" \/>\n<meta name=\"twitter:site\" content=\"@PingCAP\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Ahmad Ghazal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"11\ubd84\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.pingcap.com\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.pingcap.com\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/\"},\"author\":{\"name\":\"Ahmad Ghazal\",\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/person\/995f4823880cf5187fba2b0d6d46a689\"},\"headline\":\"Let Your Database Recommend the Indexes: Smarter Tuning in TiDB\",\"datePublished\":\"2025-05-15T14:49:39+00:00\",\"dateModified\":\"2025-05-15T14:49:40+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.pingcap.com\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/\"},\"wordCount\":1605,\"publisher\":{\"@id\":\"https:\/\/www.pingcap.com\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.pingcap.com\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/static.pingcap.com\/files\/2025\/05\/15073240\/tidb_feature_1800x600-1-1-scaled.png\",\"keywords\":[\"Distributed SQL\",\"Distributed system\",\"Index Optimization\",\"SQL\",\"TiDB\"],\"articleSection\":[\"Engineering\"],\"inLanguage\":\"ko-KR\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.pingcap.com\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/\",\"url\":\"https:\/\/www.pingcap.com\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/\",\"name\":\"Index Tuning: How TiDB Provides Smarter Recommendations\",\"isPartOf\":{\"@id\":\"https:\/\/www.pingcap.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.pingcap.com\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.pingcap.com\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/static.pingcap.com\/files\/2025\/05\/15073240\/tidb_feature_1800x600-1-1-scaled.png\",\"datePublished\":\"2025-05-15T14:49:39+00:00\",\"dateModified\":\"2025-05-15T14:49:40+00:00\",\"description\":\"TiDB 8.5 takes index tuning even further by offering intelligent, cost-based index suggestions\u2014no extra tooling required.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.pingcap.com\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/#breadcrumb\"},\"inLanguage\":\"ko-KR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.pingcap.com\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"ko-KR\",\"@id\":\"https:\/\/www.pingcap.com\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/#primaryimage\",\"url\":\"https:\/\/static.pingcap.com\/files\/2025\/05\/15073240\/tidb_feature_1800x600-1-1-scaled.png\",\"contentUrl\":\"https:\/\/static.pingcap.com\/files\/2025\/05\/15073240\/tidb_feature_1800x600-1-1-scaled.png\",\"width\":2560,\"height\":853},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.pingcap.com\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.pingcap.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Let Your Database Recommend the Indexes: Smarter Tuning in TiDB\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.pingcap.com\/#website\",\"url\":\"https:\/\/www.pingcap.com\/\",\"name\":\"TiDB\",\"description\":\"TiDB | SQL at Scale\",\"publisher\":{\"@id\":\"https:\/\/www.pingcap.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.pingcap.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"ko-KR\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.pingcap.com\/#organization\",\"name\":\"PingCAP\",\"url\":\"https:\/\/www.pingcap.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"ko-KR\",\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png\",\"contentUrl\":\"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png\",\"width\":811,\"height\":232,\"caption\":\"PingCAP\"},\"image\":{\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/facebook.com\/pingcap2015\",\"https:\/\/x.com\/PingCAP\",\"https:\/\/linkedin.com\/company\/pingcap\",\"https:\/\/youtube.com\/channel\/UCuq4puT32DzHKT5rU1IZpIA\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/person\/995f4823880cf5187fba2b0d6d46a689\",\"name\":\"Ahmad Ghazal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"ko-KR\",\"@id\":\"https:\/\/www.pingcap.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/static.pingcap.com\/files\/2023\/12\/19113432\/a732ad49-04f0-46b7-8fe6-18391f7c261f-150x150.png\",\"contentUrl\":\"https:\/\/static.pingcap.com\/files\/2023\/12\/19113432\/a732ad49-04f0-46b7-8fe6-18391f7c261f-150x150.png\",\"caption\":\"Ahmad Ghazal\"},\"description\":\"Distinguished Engineer\",\"url\":\"https:\/\/www.pingcap.com\/ko\/blog\/author\/aghazal\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Index Tuning: How TiDB Provides Smarter Recommendations","description":"TiDB 8.5 takes index tuning even further by offering intelligent, cost-based index suggestions\u2014no extra tooling required.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.pingcap.com\/ko\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/","og_locale":"ko_KR","og_type":"article","og_title":"Index Tuning: How TiDB Provides Smarter Recommendations","og_description":"TiDB 8.5 takes index tuning even further by offering intelligent, cost-based index suggestions\u2014no extra tooling required.","og_url":"https:\/\/www.pingcap.com\/ko\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/","og_site_name":"TiDB","article_publisher":"https:\/\/facebook.com\/pingcap2015","article_published_time":"2025-05-15T14:49:39+00:00","article_modified_time":"2025-05-15T14:49:40+00:00","og_image":[{"width":2400,"height":1254,"url":"https:\/\/static.pingcap.com\/files\/2025\/05\/15073411\/tidb_1200x627-1.png","type":"image\/png"}],"author":"Ahmad Ghazal","twitter_card":"summary_large_image","twitter_image":"https:\/\/static.pingcap.com\/files\/2025\/05\/15073429\/tidb_twitter_1600x900-1-scaled.png","twitter_creator":"@PingCAP","twitter_site":"@PingCAP","twitter_misc":{"Written by":"Ahmad Ghazal","Est. reading time":"11\ubd84"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.pingcap.com\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/#article","isPartOf":{"@id":"https:\/\/www.pingcap.com\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/"},"author":{"name":"Ahmad Ghazal","@id":"https:\/\/www.pingcap.com\/#\/schema\/person\/995f4823880cf5187fba2b0d6d46a689"},"headline":"Let Your Database Recommend the Indexes: Smarter Tuning in TiDB","datePublished":"2025-05-15T14:49:39+00:00","dateModified":"2025-05-15T14:49:40+00:00","mainEntityOfPage":{"@id":"https:\/\/www.pingcap.com\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/"},"wordCount":1605,"publisher":{"@id":"https:\/\/www.pingcap.com\/#organization"},"image":{"@id":"https:\/\/www.pingcap.com\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/#primaryimage"},"thumbnailUrl":"https:\/\/static.pingcap.com\/files\/2025\/05\/15073240\/tidb_feature_1800x600-1-1-scaled.png","keywords":["Distributed SQL","Distributed system","Index Optimization","SQL","TiDB"],"articleSection":["Engineering"],"inLanguage":"ko-KR"},{"@type":"WebPage","@id":"https:\/\/www.pingcap.com\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/","url":"https:\/\/www.pingcap.com\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/","name":"Index Tuning: How TiDB Provides Smarter Recommendations","isPartOf":{"@id":"https:\/\/www.pingcap.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.pingcap.com\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/#primaryimage"},"image":{"@id":"https:\/\/www.pingcap.com\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/#primaryimage"},"thumbnailUrl":"https:\/\/static.pingcap.com\/files\/2025\/05\/15073240\/tidb_feature_1800x600-1-1-scaled.png","datePublished":"2025-05-15T14:49:39+00:00","dateModified":"2025-05-15T14:49:40+00:00","description":"TiDB 8.5 takes index tuning even further by offering intelligent, cost-based index suggestions\u2014no extra tooling required.","breadcrumb":{"@id":"https:\/\/www.pingcap.com\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/#breadcrumb"},"inLanguage":"ko-KR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.pingcap.com\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/"]}]},{"@type":"ImageObject","inLanguage":"ko-KR","@id":"https:\/\/www.pingcap.com\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/#primaryimage","url":"https:\/\/static.pingcap.com\/files\/2025\/05\/15073240\/tidb_feature_1800x600-1-1-scaled.png","contentUrl":"https:\/\/static.pingcap.com\/files\/2025\/05\/15073240\/tidb_feature_1800x600-1-1-scaled.png","width":2560,"height":853},{"@type":"BreadcrumbList","@id":"https:\/\/www.pingcap.com\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.pingcap.com\/"},{"@type":"ListItem","position":2,"name":"Let Your Database Recommend the Indexes: Smarter Tuning in TiDB"}]},{"@type":"WebSite","@id":"https:\/\/www.pingcap.com\/#website","url":"https:\/\/www.pingcap.com\/","name":"\ud2f0DB","description":"TiDB | SQL at Scale","publisher":{"@id":"https:\/\/www.pingcap.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.pingcap.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"ko-KR"},{"@type":"Organization","@id":"https:\/\/www.pingcap.com\/#organization","name":"PingCAP","url":"https:\/\/www.pingcap.com\/","logo":{"@type":"ImageObject","inLanguage":"ko-KR","@id":"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/","url":"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png","contentUrl":"https:\/\/static.pingcap.com\/files\/2021\/11\/pingcap-logo.png","width":811,"height":232,"caption":"PingCAP"},"image":{"@id":"https:\/\/www.pingcap.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/facebook.com\/pingcap2015","https:\/\/x.com\/PingCAP","https:\/\/linkedin.com\/company\/pingcap","https:\/\/youtube.com\/channel\/UCuq4puT32DzHKT5rU1IZpIA"]},{"@type":"Person","@id":"https:\/\/www.pingcap.com\/#\/schema\/person\/995f4823880cf5187fba2b0d6d46a689","name":"Ahmad Ghazal","image":{"@type":"ImageObject","inLanguage":"ko-KR","@id":"https:\/\/www.pingcap.com\/#\/schema\/person\/image\/","url":"https:\/\/static.pingcap.com\/files\/2023\/12\/19113432\/a732ad49-04f0-46b7-8fe6-18391f7c261f-150x150.png","contentUrl":"https:\/\/static.pingcap.com\/files\/2023\/12\/19113432\/a732ad49-04f0-46b7-8fe6-18391f7c261f-150x150.png","caption":"Ahmad Ghazal"},"description":"Distinguished Engineer","url":"https:\/\/www.pingcap.com\/ko\/blog\/author\/aghazal\/"}]}},"grav_blocks":false,"card_markup":"<a class=\"card-resource bg-white\" href=\"https:\/\/www.pingcap.com\/ko\/blog\/let-your-database-recommend-indexes-index-tuning-tidb\/\"><div class=\"card-resource__image-container\"><img class=\"card-resource__image\" alt=\"tidb_feature_1800x600 (1)\" src=\"https:\/\/static.pingcap.com\/files\/2025\/05\/15073240\/tidb_feature_1800x600-1-1-scaled.png\" loading=\"lazy\" width=2560 height=853 \/><\/div><div class=\"card-resource__content-container\"><div class=\"card-resource__content-head\"><div class=\"card-resource__category\">Engineering<\/div><\/div><h5 class=\"card-resource__title\">Let Your Database Recommend the Indexes: Smarter Tuning in TiDB<\/h5><\/div><\/a>","_links":{"self":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/posts\/27341","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/users\/256"}],"replies":[{"embeddable":true,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/comments?post=27341"}],"version-history":[{"count":19,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/posts\/27341\/revisions"}],"predecessor-version":[{"id":27369,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/posts\/27341\/revisions\/27369"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/media\/27357"}],"wp:attachment":[{"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/media?parent=27341"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/categories?post=27341"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.pingcap.com\/ko\/wp-json\/wp\/v2\/tags?post=27341"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}