If you create a datetime index, it is very likely that this index will be used, and the performance of using this index is poor

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

Original topic: 如果你建了datetime索引 大概率都会走这个索引 而走这个索引性能很差

| username: tidb狂热爱好者

[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]
If you create a datetime index, other SQL queries using the datetime index will likely also use this index, and the performance will be poor when using this index.
I want to ask why.
How to optimize?

| username: h5n1 | Original post link

Get rid of it.

| username: Qiuchi | Original post link

Is this datetime a range or a fixed value in the query? Can you use a hint or use index to disable that datetime index and then send an explain analyze for observation?

| username: Running | Original post link

It is also related to the repetition of time. You can use DISTINCT on the time field to see how many duplicate times there are.

| username: WalterWj | Original post link

| username: dba-kit | Original post link

Did you add “order by datetime” at the end? In my practice, there aren’t that many cases of using the wrong index.

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

Most of them have added an order by.

| username: 胡杨树旁 | Original post link

How is the datetime data distributed?

| username: TI表弟 | Original post link

Time can be stored in milliseconds, and there won’t be much duplication with microseconds.

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

It is generally not recommended to create indexes on time fields because if the statistical information is not up-to-date, the time field index can mislead the optimizer.