TiDB Index Failure

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

Original topic: TIDB 索引失效

| username: 大飞飞呀

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version]
[Reproduction Path] What operations were performed to encounter the issue

[Encountered Issue: Problem Phenomenon and Impact]

select   count(1) from 
 table_a a  ,
      1096000160009428992 user_id,
      "2023-09-06" c_date
      1095993953290952704 user_id,
      "2023-09-06" c_date
  ) b
      a.created_at >= b.c_date
  AND a.created_at <  DATE_ADD(b.c_date, INTERVAL 1 DAY)

Why is the created_at index of table_a in this SQL a fullIndexScan?

[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]

| username: xfworld | Original post link

How is the index created?

| username: zhanggame1 | Original post link

I don’t see any issues.

| username: 人如其名 | Original post link

Based on what you said, the number of records in table a is very small, and the amount of data in table b is also very small after filtering by the create_at range.

The ideal approach for the name column is to use IndexLookupJoin on table b and table a, with table a using the create_at index.

Unfortunately, TiDB’s IndexLookupJoin does not support non-equi join conditions, so it can only use hash join.

| username: Jellybean | Original post link

What are the indexes of this table, and part of the execution plan screenshot is missing. Please post all of it so we can take a look.

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

This is normal because you are using a join condition. Imagine if there are a lot of data in table B with inconsistent timestamps, then the created_at in table A can only be fully scanned by the index.