SHOW INDEX

SHOW INDEX returns index information for a PolarDB-X 1.0 table, including local secondary indexes (LSIs) and global secondary indexes (GSIs). SHOW INDEXES and SHOW KEYS are aliases for this statement.

Syntax

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

Example

mysql> SHOW INDEX FROM t_order;

Output:

+---------+------------+-----------+--------------+----------------+-----------+-------------+----------+--------+------+------------+----------+---------------+
| 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)

To display each row vertically — useful when the table has many columns — append \G:

mysql> SHOW INDEX FROM t_order\G
*************************** 1. row ***************************
        TABLE: t_order
   NON_UNIQUE: 0
     KEY_NAME: PRIMARY
 SEQ_IN_INDEX: 1
  COLUMN_NAME: id
    COLLATION: A
  CARDINALITY: 0
     SUB_PART: NULL
       PACKED: NULL
         NULL:
   INDEX_TYPE: BTREE
      COMMENT:
INDEX_COMMENT:
...
6 rows in set (0.01 sec)

Output columns

Column	Description
`TABLE`	The name of the table.
`NON_UNIQUE`	Whether the index allows duplicate values. `1` means duplicates are allowed (non-unique index); `0` means each value must be unique.
`KEY_NAME`	The name of the index.
`SEQ_IN_INDEX`	The position of the column within the index. Starts at `1`.
`COLUMN_NAME`	The name of the indexed column.
`COLLATION`	The sort order of the column in the index. Valid values: `A` (ascending), `D` (descending), `NULL` (unsorted).
`CARDINALITY`	The estimated number of unique values in the index.
`SUB_PART`	The index prefix length, in characters. `NULL` means the entire column is indexed.
`PACKED`	How the key value is packed. `NULL` means the key is not packed.
`NULL`	Whether the column can contain `NULL` values. `YES` if it can; blank if it cannot.
`INDEX_TYPE`	The index structure. Valid values: `BTREE`, `HASH`, `NULL` (not specified).
`COMMENT`	The role of this row in the index. `NULL` or blank indicates a local index. For GSIs, `INDEX` identifies the index column and `COVERING` identifies a covering column — an extra column stored in the GSI to avoid lookups back to the primary table.
`INDEX_COMMENT`	Any additional comment specified when the index was created.

Syntax

Example

Output columns

See also