Query indexes

This topic describes how to query indexes.

You can execute the SHOW INDEX statement to query the indexes of a table.

Example: Query the indexes of the test table.

obclient> SHOW INDEX FROM test;
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| Table       | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
| test        |          0 | PRIMARY  |            1 | w_id        | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
| test        |          0 | w_name   |            1 | w_name      | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     |
| test        |          0 | w_name   |            2 | w_city      | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
3 rows in set

Fields in the query result are described as follows:

• Non_unique: If the index cannot include duplicate values, the value of the field is 0. Otherwise, the value is 1. In other words, the value 0 indicates a unique index.

• Key_name: the name of the index.

• Seq_in_index: the sequence number of the column in the composite index, such as 1 or 2.

• 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 is NULL if the entire column is indexed.

• Packed: the compression method of keywords. If keywords are not compressed, the value is NULL.

• Index_type: the index type. At present, only the BTREE type is supported.

• Comment: indicates whether the index is available.

• Index_comment: the comment for the index.

• Visible: indicates whether the index is visible.

References

For more information about indexes supported by the MySQL mode of OceanBase Database, see the following topics:

• About indexes

• Create an index

• Create an index on a partitioned table

• Drop an index