TIDB_INDEXES

The TIDB_INDEXES table provides the INDEX information of all tables.

USE information_schema;
DESC tidb_indexes;

+---------------+---------------+------+------+---------+-------+
| Field         | Type          | Null | Key  | Default | Extra |
+---------------+---------------+------+------+---------+-------+
| TABLE_SCHEMA  | varchar(64)   | YES  |      | NULL    |       |
| TABLE_NAME    | varchar(64)   | YES  |      | NULL    |       |
| NON_UNIQUE    | bigint(21)    | YES  |      | NULL    |       |
| KEY_NAME      | varchar(64)   | YES  |      | NULL    |       |
| SEQ_IN_INDEX  | bigint(21)    | YES  |      | NULL    |       |
| COLUMN_NAME   | varchar(64)   | YES  |      | NULL    |       |
| SUB_PART      | bigint(21)    | YES  |      | NULL    |       |
| INDEX_COMMENT | varchar(2048) | YES  |      | NULL    |       |
| Expression    | varchar(64)   | YES  |      | NULL    |       |
| INDEX_ID      | bigint(21)    | YES  |      | NULL    |       |
| IS_VISIBLE    | varchar(64)   | YES  |      | NULL    |       |
| CLUSTERED     | varchar(64)   | YES  |      | NULL    |       |
+---------------+---------------+------+------+---------+-------+
12 rows in set (0.00 sec)

INDEX_ID is the unique ID that TiDB allocates for each index. It can be used to do a join operation with INDEX_ID obtained from another table or API.

For example, you can obtain TABLE_ID and INDEX_ID that are involved in some slow query in the SLOW_QUERY table and then obtain the specific index information using the following SQL statements:

SELECT
 tidb_indexes.*
FROM
 tidb_indexes,
 tables
WHERE
  tidb_indexes.table_schema = tables.table_schema
 AND tidb_indexes.table_name = tidb_indexes.table_name
 AND tables.tidb_table_id = ?
 AND index_id = ?

Fields in the TIDB_INDEXES table are described as follows:

• TABLE_SCHEMA: The name of the schema to which the index belongs.
• TABLE_NAME: The name of the table to which the index belongs.
• NON_UNIQUE: If the index is unique, the value is 0; otherwise, the value is 1.
• KEY_NAME: The index name. If the index is the primary key, the name is PRIMARY.
• SEQ_IN_INDEX: The sequential number of columns in the index, which starts from 1.
• COLUMN_NAME: The name of the column where the index is located.
• SUB_PART: The prefix length of the index. If the the column is partly indexed, the SUB_PART value is the count of the indexed characters; otherwise, the value is NULL.
• INDEX_COMMENT: The comment of the index, which is made when the index is created.
• INDEX_ID: The index ID.
• IS_VISIBLE: Whether the index is visible.
• CLUSTERED: Whether it is a clustered index.