Identifying Unused Indexes

Translated
1

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

Original topic: 查找用不到的索引

| username: weibiao

[Question] Current Issue
I want to be able to find redundant and unused indexes, just like in MySQL.

[Business Impact]

[TiDB Version] v5.2.2

[Application Software and Version]

[Attachments] Relevant logs and configuration information

2
| username: cs58_dba | Original post link

Refer to this post: TiDB 索引优化 - TiDB 的问答社区

3
| username: 啦啦啦啦啦 | Original post link

TiDB currently does not have system tables similar to schema_redundant_indexes and schema_unused_indexes under the MySQL sys schema. At present, you can use the dashboard to optimize slow SQL queries.

4
| username: ShawnYan | Original post link

Currently, you can check this table information_schema.tidb_indexes, and you need to determine the results yourself.

5
| username: ealam_小羽 | Original post link

INFORMATION_SCHEMA.SCHEMA_INDEX_USAGE

Reference: tidb/docs/design/2020-09-30-index-usage-information.md at master · pingcap/tidb · GitHub

But it’s best to search before asking: TiDB 索引优化 - #7，来自 ealam_小羽 - TiDB 的问答社区, this post already has similar questions.

6
| username: 啦啦啦啦啦 | Original post link

Is this feature implemented? My table here is empty.

7
| username: ShawnYan | Original post link 
mysql> select version()\G
*************************** 1. row ***************************
version(): 5.7.25-TiDB-v6.2.0-alpha
1 row in set (0.00 sec)

mysql> select * from mysql.SCHEMA_INDEX_USAGE;
Empty set (0.00 sec)

mysql> select * from INFORMATION_SCHEMA.SCHEMA_INDEX_USAGE;
ERROR 1146 (42S02): Table 'information_schema.SCHEMA_INDEX_USAGE' doesn't exist
8
| username: 啦啦啦啦啦 | Original post link

In the MySQL database, but it seems there is no data.

9
| username: ShawnYan | Original post link

Well, what the person above wrote is I_S. The main point here is that the sys schema unused index feature in MySQL 8 has not yet been implemented in TiDB.

10
| username: ealam_小羽 | Original post link

My bad, it seems it hasn’t been implemented yet. I thought it was because I was using version 4.0.16.

11
| username: cs58_dba | Original post link

To be honest, MySQL is not used that often either.

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

You can actually write a SQL query to reverse check for unused indexes.

13
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. No new replies are allowed.