Query indexes

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

The syntax is as follows:

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

Use views to query index information

• You can query the information_schema.STATISTICS view to obtain information about indexes of a table, such as the table name, whether the index is unique, the index name, the index ID, and the column name.

• You can query the oceanbase.DBA_OB_TABLE_LOCATIONS view to obtain whether the index is a global index (GLOBAL) or a local index (LOCAL).

Here is an example:

1. Query the views of the tbl1 table.

   SELECT TABLE_SCHEMA, TABLE_NAME, SEQ_IN_INDEX, INDEX_NAME, COLUMN_NAME
   FROM information_schema.STATISTICS
   WHERE TABLE_NAME = 'tbl1';
   

   The return result is as follows:

   +--------------+------------+--------------+------------+-------------+
   | TABLE_SCHEMA | TABLE_NAME | SEQ_IN_INDEX | INDEX_NAME | COLUMN_NAME |
   +--------------+------------+--------------+------------+-------------+
   | test         | tbl1       |            1 | tbl1_idx1  | col1        |
   | test         | tbl1       |            2 | tbl1_idx1  | col2        |
   | test         | tbl1       |            1 | tbl1_idx2  | col3        |
   +--------------+------------+--------------+------------+-------------+
   3 rows in set
   

2. Query whether the tbl1_idx1 index is a global index (GLOBAL) or a local index (LOCAL).

   SELECT DATABASE_NAME, INDEX_NAME, INDEX_TYPE
   FROM oceanbase.DBA_OB_TABLE_LOCATIONS
   WHERE INDEX_NAME = 'tbl1_idx1';
   

   The return result is as follows:

   +---------------+------------+------------+
   | DATABASE_NAME | INDEX_NAME | INDEX_TYPE |
   +---------------+------------+------------+
   | test          | tbl1_idx1  | LOCAL      |
   +---------------+------------+------------+
   1 row in set
   

3. Query the index information of the tbl1 table from the information_schema.STATISTICS and oceanbase.DBA_OB_TABLE_LOCATIONS views.

   SELECT ss.TABLE_SCHEMA, ss.TABLE_NAME, ss.SEQ_IN_INDEX, ss.INDEX_NAME, ss.COLUMN_NAME, ls.INDEX_TYPE
   FROM information_schema.STATISTICS ss, oceanbase.DBA_OB_TABLE_LOCATIONS ls
   WHERE ss.TABLE_SCHEMA = ls.DATABASE_NAME
     AND ss.INDEX_NAME = ls.INDEX_NAME
     AND ss.TABLE_NAME = 'tbl1';
   

   The return result is as follows:

   +--------------+------------+--------------+------------+-------------+------------+
   | TABLE_SCHEMA | TABLE_NAME | SEQ_IN_INDEX | INDEX_NAME | COLUMN_NAME | INDEX_TYPE |
   +--------------+------------+--------------+------------+-------------+------------+
   | test         | tbl1       |            2 | tbl1_idx1  | col2        | LOCAL      |
   | test         | tbl1       |            1 | tbl1_idx1  | col1        | LOCAL      |
   | test         | tbl1       |            1 | tbl1_idx2  | col3        | LOCAL      |
   +--------------+------------+--------------+------------+-------------+------------+
   3 rows in set
   

References

For more information about indexes in 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