Simple Query in TiDB Uses Index, WAIT_TS Time Exceeds 500ms

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

Original topic: TiDB 简单查询 走索引,WAIT_TS 时间500ms以上

| username: 大飞飞呀

【TiDB Usage Environment】Production Environment
【TiDB Version】5.14
【Reproduction Path】What operations were performed to cause the issue
SELECT * FROM table WHERE cc_id = ‘cdjcksncdni’ AND IS_DELETE = 0 AND aa_ID = 1 AND I_STATUS = 2;
【Encountered Issue: Issue Phenomenon and Impact】
Table size is over 1 million, cc_id has an index, wait_ts 500ms, why is this happening?
【Resource Configuration】Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
【Attachments: Screenshots/Logs/Monitoring】

| username: tidb狂热爱好者 | Original post link

The index needs to consider the generalization effect. If everyone uses ‘cdjcksncdni’, the effect will be poor. The second index is best applied to numbers.

| username: 大飞飞呀 | Original post link

wait_ts is slow, it shouldn’t be related to the index, right?

| username: zhanggame1 | Original post link

Take a look at the execution plan, and you can also trace this SQL to see the specific timing issue.

| username: tony5413 | Original post link

wait_ts is used to represent the timestamp of the current transaction’s wait time, that is, the current time when the transaction is waiting to be committed or rolled back.

| username: linnana | Original post link

Explain the execution plan and check if the index field has high cardinality.

| username: redgame | Original post link

It might also be due to high load at that time, try again at a different time.