You can use the SHOW INDEX/INDEXES/KEYS statement to view the indexes of a table.
The statement is as follows:
SHOW [EXTENDED] {INDEX | INDEXES | KEYS}
{FROM | IN} table_name
[{FROM | IN} database_name];
In the statement:
EXTENDED: Displays hidden indexes.table_name: The name of the table whose indexes are to be viewed.database_name: The database to which the table belongs.
Examples:
View the indexes of the
testtable.obclient [test]> SHOW INDEX FROM test;The query result is as follows:
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+ | test | 1 | IDX | 1 | id | A | NULL | NULL | NULL | YES | BTREE | available | | YES | NULL | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+ 1 row in setThe fields in the query result are described as follows:
Non_unique: If the index cannot include duplicate values, the value of the field is0. Otherwise, the value is1. In other words, the value0indicates a unique index.Key_name: The name of the index.Seq_in_index: The sequence number of the column in the composite index, such as1or2.Column_name: The name of the indexed column.Collation: The collation that indicates how columns are stored in the index.Cardinality: The estimated number of unique values in the index.Sub_part: Indicates a prefixed index. If the column is partially indexed, the value is the number of indexed characters in the column. The value isNULLif the entire column is indexed.Packed: The compression method of keywords. If keywords are not compressed, the value isNULL.Null: Indicates whether the index value can be null.Index_type: The index type. At present, only theBTREEtype is supported.Comment: Indicates whether the index is available.Index_comment: The comment for the index.Visible: Indicates whether the index is visible.
View all indexes in the
testtable, including hidden indexes.obclient [test]> SHOW EXTENDED INDEX FROM test;The query result is as follows:
+-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression | +-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+ | test | 0 | PRIMARY | 1 | __pk_increment | A | NULL | NULL | NULL | | BTREE | available | | YES | NULL | | test | 0 | PRIMARY | 2 | id | A | NULL | NULL | NULL | | BTREE | available | | YES | NULL | | test | 0 | PRIMARY | 3 | name | A | NULL | NULL | NULL | | BTREE | available | | YES | NULL | | test | 1 | IDX | 1 | id | A | NULL | NULL | NULL | YES | BTREE | available | | YES | NULL | | test | 1 | IDX | 2 | __pk_increment | A | NULL | NULL | NULL | | BTREE | available | | YES | NULL | +-------+------------+----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+ 5 rows in set
For more information about the SHOW INDEX statement, see SHOW.
References
For more information about indexes in MySQL-compatible mode, see the following topics: