How to Query Unused Indexes

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

Original topic: 如何查询没有用过的索引

| username: 路在何chu

[TiDB Usage Environment] Production Environment 4013
[Reproduction Path]
Which view can be used to query unused indexes? I couldn’t find it and am thinking about deleting unused indexes.

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

This feature is currently not available.

| username: 路在何chu | Original post link

Is this feature unnecessary? Having more indexes in TiDB does not affect insertion, unlike MySQL.

| username: Kongdom | Original post link

:joy: You can’t just delete it. Just because it’s not needed now doesn’t mean it won’t be needed in the future. Some indexes are reserved for special scenarios.

| username: 小龙虾爱大龙虾 | Original post link

Currently, TiDB does not have a dedicated feature to record this, but you can obtain usage information from the INDEX_NAMES field in cluster_slow_query and STATEMENTS_SUMMARY_HISTORY. If the data in STATEMENTS_SUMMARY_HISTORY has not been evicted, it can be understood as the index usage over a period of time.

| username: Jellybean | Original post link

Think twice before modifying the table’s index, especially making sure to confirm with all business users of the table in advance.

| username: 路在何chu | Original post link

Well, if this feature is not available, then forget it.

| username: swino | Original post link

There is no corresponding view to query.

| username: 像风一样的男子 | Original post link

This feature doesn’t exist. Actually, it would be very useful. The request has been made, but it seems there is no schedule for it.

| username: dba远航 | Original post link

First, set it to be invisible, and then execute the statement. If the execution efficiency of the statement is not affected, it means the index is not being used.

| username: 大飞哥online | Original post link

There is no such statistical information. You can check if there are duplicate indexes and optimize them.
Make the index invisible, and if no one complains after a while, then you can delete it, hahaha.

| username: Kongdom | Original post link

:yum: Changing the perspective, if we subtract the indexes used over a period of time from the existing indexes, wouldn’t that give us the unused indexes?

Currently, it seems we can only count the indexes used over a period of time.

| username: Kongdom | Original post link

You understand operations :yum:

| username: 大飞哥online | Original post link

Hahaha, absolutely :stuck_out_tongue_closed_eyes:

| username: 像风一样的男子 | Original post link

This is too dangerous. If there is a SQL that is executed after a long time and it finds that the index is gone, the cluster will crash if there is a high concurrency.

| username: TiDBer_小阿飞 | Original post link

Haha, this looks like getting ready for a feast :sweat_smile:

| username: hazatax | Original post link

No matter what database it is, indexes will definitely affect writes, right?

| username: forever | Original post link

Not only does it affect writing, but the space is also huge. For some tables, the index space is even larger than the table itself.

| username: andone | Original post link

This feature is not available at the moment.

| username: zhanggame1 | Original post link

Adding an index in TiDB has a greater impact on write performance.