Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: TIDB like ‘ABC%’ 不走索引
[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version]
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Problem Phenomenon and Impact]
Found that in the database execution plan, like ‘ABC%’ % cannot use the index and can only perform a full table scan. Please help analyze the reason.
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]
Post the table structure and execution plan.
Please share the table structure for review.
Also, please provide the data volume. If the number of data rows is too small, the index will not be used.
Inaccurate statistics, table size and data distribution, index selectivity, query complexity, and other factors all have an impact, leading to not using the index. Check the execution plan for analysis.
Check if the statistics are accurate, or if there are too many selected fields, causing the query index to go back to the table. It might be faster to directly scan the entire table.
Explain and explain analyze analysis
Check if the index is effective, review the table’s metadata and health status using show status_meta and show status_healthy, and see if the statistics need to be updated.
How about running explain analyze?
Accuracy of statistical information is 100%.
The image is not visible. Please provide the text you need translated.
Try binding to force a specific index and see the effect.
EXPLAIN ANALYZE SELECT /*+ use_index('alias', 'index_name') */ * FROM t2 as alias WHERE a > 1 AND b = 1;
– Create binding
– Effective usage of Hint
CREATE GLOBAL BINDING for SELECT * FROM t2 as alias WHERE a > 1 AND b = 1
USING SELECT /*+ use_index('alias', 'index_name') */ * FROM t2 as alias WHERE a > 1 AND b = 1;
– Query binding
SHOW GLOBAL BINDINGS;
– Delete binding
DROP GLOBAL BINDING FOR SELECT * FROM t2 WHERE a > 1 AND b = 1;
This is a slow table lookup. It is recommended to use a composite index to filter most of the data.
Can you also post the sentence? It looks like another index was chosen, and it seems like a SQL query can only use one index.
Will using LIKE filter more data than using start_time? If not, then using start_time is fine for now.
start_time is a range value, and even if it is a composite index with the LIKE field, it cannot use this index.
If using LIKE is more optimal, then use a hint to bind the index of the LIKE field and force it to use LIKE.
Moreover, considering the entire SQL, using an index join for table l and table d should be better, right?
It seems to be using an index (range). Is it a partitioned table, crossing partitions, and then accessing the table again?
What is the specific SQL, the table data volume, and the result set data volume?
It is best to try tiflash + mpp.
When the root operator is hashagg, the improvement will be significant.
First, try to force it with a HINT. If it works, reanalyze the table statistics.
|
username: lemonade010
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: