The query found that the composite index was not used

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 查询发现联合索引没走

| username: 快乐的非鱼

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version]
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]

A composite index was created on a table with millions of records, all of which are char(). However, a simple query does not use this index and instead performs a full table scan. Health checks and admin checks were done, but the specific reason is unclear. The query takes 4 seconds, while the same query on SQL Server with more data takes only a little over 2 seconds.
City char(36) COLLATE utf8mb4_general_ci DEFAULT NULL,
Version char(36) COLLATE utf8mb4_general_ci DEFAULT NULL,

KEY idx_city_version (City,Version)

image
image927×160 5.43 KB

2
| username: tidb菜鸟一只 | Original post link

Please send the SQL…

3
| username: 快乐的非鱼 | Original post link

It’s just a very simple SQL: SELECT * FROM tablename WHERE city='**' AND version='**'.

4
| username: xingzhenxiang | Original post link

SHOW STATS_HEALTHY to check the status of the table

5
| username: 快乐的非鱼 | Original post link

Healthy, it was 100% when I wrote the post just now, now it’s 93.

6
| username: 快乐的非鱼 | Original post link

I think I know the reason. The original query condition returned a lot of records, probably tens of thousands of rows. The system likely considered the data volume to be very large, so even though there was an index, it performed a full table scan. After I reduced the records matching this condition to only about 7,000, the EXPLAIN showed that it used the index.

7
| username: Hacker007 | Original post link

Manually execute ANALYZE TABLE, or wait for it to execute automatically.

8
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.