Important

You are viewing the documentation of an older version of the TiDB database (TiDB v5.3).

It is recommended that you use the latest LTS version of the TiDB database.

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    |       |
+---------------+---------------+------+------+---------+-------+
10 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.
Was this page helpful?