This topic describes how to use the SHOW INDEX statement to view local secondary indexes (LSIs) and global secondary indexes (GSIs) of PolarDB-X 1.0 tables.

Syntax

SHOW {INDEX | INDEXES | KEYS}
    {FROM | IN} tbl_name
    [{FROM | IN} db_name]
    [WHERE expr]

Examples

mysql> show index from t_order;
+--------------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
| TABLE   | NON_UNIQUE | KEY_NAME  | SEQ_IN_INDEX | COLUMN_NAME    | COLLATION | CARDINALITY | SUB_PART | PACKED | NULL | INDEX_TYPE | COMMENT  | INDEX_COMMENT |
+--------------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
| t_order |          0 | PRIMARY   |            1 | id             | A         |           0 |     NULL | NULL   |      | BTREE      |          |               |
| t_order |          1 | l_i_order |            1 | order_id       | A         |           0 |     NULL | NULL   | YES  | BTREE      |          |               |
| t_order |          0 | g_i_buyer |            1 | buyer_id       | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | INDEX    |               |
| t_order |          1 | g_i_buyer |            2 | id             | NULL      |           0 |     NULL | NULL   |      | GLOBAL     | COVERING |               |
| t_order |          1 | g_i_buyer |            3 | order_id       | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | COVERING |               |
| t_order |          1 | g_i_buyer |            4 | order_snapshot | NULL      |           0 |     NULL | NULL   | YES  | GLOBAL     | COVERING |               |
+--------------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
6 rows in set (0.01 sec)
Table 1. List of column names
Column name Description
TABLE The name of the table.
NON_UNIQUE Indicates whether the index is a unique GSI. Valid values:
  • 1: a common GSI
  • 0: a unique GSI
KEY_NAME The name of the index.
SEQ_IN_INDEX The sequence number of the index column in the index. The value starts from 1.
COLUMN_NAME The name of the index column.
COLLATION The sorting order. Valid values:
  • A: ascending order
  • D: descending order
  • NULL: not sorted
CARDINALITY The number of estimated unique values.
SUB_PART The prefix of the index. NULL indicates that the prefix of the index is the entire column.
PACKED The information about field compression. NULL indicates no compression.
NULL Indicates whether the column can be empty.
INDEX_TYPE The index type. Valid values:
  • NULL: not specified
  • BTREE
  • HASH
COMMENT The index information. Valid values:
  • NULL: local index
  • INDEX: the index column of the GSI
  • COVERING: the covering column of the GSI
INDEX_COMMENT Other information of the index.