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 results:

• 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 a column in a composite index, such as 1 or 2.

• Column_name: the name of an 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: the prefix length of the prefix index. If the column is partly indexed, the value of this field is the number of indexed characters. 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.

More information

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

• Overview

• Create an index

• Create an index on a partitioned table

• Drop an index