Query indexes

You can use the SHOW INDEX/INDEXES/KEYS statement to query the indexes of a table.

Here is a sample statement:

SHOW [EXTENDED] {INDEX | INDEXES | KEYS}
    {FROM | IN} table_name
    [{FROM | IN} database_name];

where:

• EXTENDED specifies to show hidden indexes.

• table_name specifies the name of the table whose indexes are to be queried.

• database_name specifies the name of the database to which the table belongs.

Here are two examples:

• Query the indexes of the test table.

  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 set
  

The 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.

• Null: indicates whether the index value can be 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.

• Query all indexes, including hidden indexes, of the test table.

  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 the SHOW INDEX statement, see SHOW.

References

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

• Overview

• Create an index

• Create an index on a partitioned table

• Drop an index