B-tree Index Failure Between Datetime Type Fields

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

Original topic: datetime类型字段间btree索引失效

| username: TiDBer_qOCdpBz3

[TiDB Usage Environment] Production Environment
[TiDB Version] 6.3
[Reproduction Path] Operations performed that led to the issue
[Encountered Issue: Problem Phenomenon and Impact]
B-tree index on datetime type field is invalid



The statement is as follows:
EXPLAIN ANALYZE
SELECT
count( a.id )
FROM
t_audio_backup a
WHERE
a.receive_time >= ‘2023-06-05 00:00:00’
AND a.receive_time <= ‘2023-06-05 23:59:59’
AND a.file_name IS NOT NULL
AND a.file_name != “”
AND a.gjz_is_spoof =1
[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]

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

Collect the statistics of the table with the command analyze table t_audio_backup; and then check the SQL.

| username: zhanggame1 | Original post link

Try removing these conditions to see if the index is used. The index hasn’t become invalid; it’s just using a different one.

| username: TiDBer_qOCdpBz3 | Original post link

After executing analyze, there is no significant change in the SQL execution content.

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

Can a time field filter condition alone use an index?

| username: TiDBer_qOCdpBz3 | Original post link

No. It should be because a composite primary key is set, other time field indexes can be used.

| username: zhanggame1 | Original post link

Try using a hint.

| username: 春风十里 | Original post link

SHOW CREATE TABLE table_name \G
Take a look

| username: cy6301567 | Original post link

Have you collected statistics? Try using “use index” forcibly and see.

| username: redgame | Original post link

Still collecting statistics.