Is there currently a feature in TiDB to check the usage frequency of indexes?

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

Original topic: 目前tidb有可用的查看索引使用次数的功能吗

| username: zzw6776

The scenario is to find and delete infrequently used (duplicate) indexes to free up space.

I searched the forum and found two posts discussing this issue:

The solution provided in this post is ineffective. So far, the data in the mysql.SCHEMA_INDEX_USAGE table is empty (is this feature not implemented?).

The second post:

The SQL provided in this post can be executed:

select `information_schema`.`statistics`.`TABLE_SCHEMA` AS `table_schema`,`information_schema`.`statistics`.`TABLE_NAME` AS `table_name`,`information_schema`.`statistics`.`INDEX_NAME` AS `index_name`,max(`information_schema`.`statistics`.`NON_UNIQUE`) AS `non_unique`,max(if(isnull(`information_schema`.`statistics`.`SUB_PART`),0,1)) AS `subpart_exists`,group_concat(`information_schema`.`statistics`.`COLUMN_NAME` order by `information_schema`.`statistics`.`SEQ_IN_INDEX` ASC separator ',') AS `index_columns` from `information_schema`.`statistics` where ((`information_schema`.`statistics`.`INDEX_TYPE` = 'BTREE') and (`information_schema`.`statistics`.`TABLE_SCHEMA` not in ('mysql','sys','INFORMATION_SCHEMA','PERFORMANCE_SCHEMA'))) group by `information_schema`.`statistics`.`TABLE_SCHEMA`,`information_schema`.`statistics`.`TABLE_NAME`,`information_schema`.`statistics`.`INDEX_NAME`

However, the original poster asked about MySQL, not TiDB. Does this SQL have the same effect in TiDB?

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

Currently, TiDB does not record this. In MySQL, the usage of indexes is mainly recorded in the performance_schema.table_io_waits_summary_by_index_usage table, but TiDB does not record this.

| username: Kongdom | Original post link

I checked it, and it can be found, but I’m not sure if it’s the effect you want.

| username: Kongdom | Original post link

The method given in your first post can find it out, right? I can find it out by executing this statement.

SELECT 
	INDEX_NAMES, EXEC_COUNT 
FROM INFORMATION_SCHEMA.statements_summary
WHERE INDEX_NAMES IS NOT NULL
| username: redgame | Original post link

A reference is enough.

| username: zhanggame1 | Original post link

The SQL you posted is not useful in TiDB.

| username: zhanggame1 | Original post link

I tried but couldn’t find anything, it returned 0 rows.

| username: Kongdom | Original post link

It is possible that your current queries are not using indexes.
image

| username: zhanggame1 | Original post link

After some research, I found that TiDB uses clustered indexes by default for primary keys, which cannot be counted. However, additional secondary indexes can be used to retrieve the data.

| username: Kongdom | Original post link

The second line in my screenshot is the primary key index.

| username: zhanggame1 | Original post link

There is an issue with the primary key index statistics. You can check how many times data is queried using this primary key index. The value found here will not increase, while the secondary index counts normally.

| username: zzw6776 | Original post link

This table seems incomplete. There are only over 400 records in total. If you add the condition INDEX_NAMES IS NOT NULL, there are only 70 records left. I checked the timestamps, and they are all from the same day.

| username: Kongdom | Original post link

It feels like it’s been recent.

| username: TiDB_C罗 | Original post link

I followed the example of mysql.sys.schema_redundant_indexes to filter some redundant indexes. Currently, it seems that unused indexes cannot be queried. Additionally, when optimizing redundant indexes by deleting an index, you can first rename it to avoid issues with queries using force index. If any queries report errors, you can rename it back to the original name without needing to recreate the index:

SELECT
    redundant_keys.table_schema,
    redundant_keys.table_name,
    redundant_keys.KEY_NAME AS redundant_index_name,
    redundant_keys.index_columns AS redundant_index_columns,
    redundant_keys.non_unique AS redundant_index_non_unique,
    dominant_keys.KEY_NAME AS dominant_index_name,
    dominant_keys.index_columns AS dominant_index_columns,
    dominant_keys.non_unique AS dominant_index_non_unique,
    IF(redundant_keys.subpart_exists OR dominant_keys.subpart_exists, 1 ,0) AS subpart_exists,
    CONCAT(
      'ALTER TABLE `', redundant_keys.table_schema, '`.`', redundant_keys.table_name, '` DROP INDEX `', redundant_keys.KEY_NAME, '`'
      ) AS sql_drop_index
  FROM
    (SELECT
        TABLE_SCHEMA,
        TABLE_NAME,
        KEY_NAME,
        MAX(NON_UNIQUE) AS non_unique,
        MAX(IF(SUB_PART IS NULL, 0, 1)) AS subpart_exists,
        GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS index_columns
    FROM INFORMATION_SCHEMA.TIDB_INDEXES
    WHERE
        TABLE_SCHEMA NOT IN ('mysql', 'sys', 'INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA', 'METRICS_SCHEMA')
    GROUP BY
        TABLE_SCHEMA, TABLE_NAME, KEY_NAME) AS redundant_keys
    INNER JOIN 
    (SELECT
        TABLE_SCHEMA,
        TABLE_NAME,
        KEY_NAME,
        MAX(NON_UNIQUE) AS non_unique,
        MAX(IF(SUB_PART IS NULL, 0, 1)) AS subpart_exists,
        GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS index_columns
    FROM INFORMATION_SCHEMA.TIDB_INDEXES
    WHERE
        TABLE_SCHEMA NOT IN ('mysql', 'sys', 'INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA', 'METRICS_SCHEMA')
    GROUP BY
        TABLE_SCHEMA, TABLE_NAME, KEY_NAME

    ) AS dominant_keys
    USING (TABLE_SCHEMA, TABLE_NAME)
  WHERE
    redundant_keys.KEY_NAME != dominant_keys.KEY_NAME
    AND (
      ( 
        /* Identical columns */
        (redundant_keys.index_columns = dominant_keys.index_columns)
        AND (
          (redundant_keys.non_unique > dominant_keys.non_unique)
          OR (redundant_keys.non_unique = dominant_keys.non_unique 
          	AND IF(redundant_keys.KEY_NAME='PRIMARY', '', redundant_keys.KEY_NAME) > IF(dominant_keys.KEY_NAME='PRIMARY', '', dominant_keys.KEY_NAME)
          )
        )
      )
      OR
      ( 
        /* Non-unique prefix columns */
        LOCATE(CONCAT(redundant_keys.index_columns, ','), dominant_keys.index_columns) = 1
        AND redundant_keys.non_unique = 1
      )
      OR
      ( 
        /* Unique prefix columns */
        LOCATE(CONCAT(dominant_keys.index_columns, ','), redundant_keys.index_columns) = 1
        AND dominant_keys.non_unique = 0
      )
    );
| username: 路在何chu | Original post link

No, I have looked for this feature before and couldn’t find it.

| username: 江湖故人 | Original post link

Brother’s script is quite practical and can be submitted to the system for review.

| username: FutureDB | Original post link

You can try the historical table INFORMATION_SCHEMA.statements_summary_history in INFORMATION_SCHEMA.statements_summary.