Join the SingleStore Community Today
Get expert advice, develop skills, and connect with others.

SHOW INDEXES

Show the list of indexes associated with a given table.

Syntax

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

Remarks

  • tbl_name - name of a table in a SingleStore DB database.
  • db_name - name of a SingleStore DB database.
  • expr - SQL WHERE expression.
  • This command must be run on the master aggregator or a child aggregator node (see Node Requirements for SingleStore DB Commands).

Output

Column Description
Table Table name
Non_unique 1 if non-unique, 0 if unique
Key_name Index (key) name
Seq_in_index Ordinal position of key column in index
Column_name Column name (keys can have one or more columns)
Collation Collation of string column, if applicable
Cardinality Always NULL (not applicable in SingleStore DB)
Sub_part Always NULL (not applicable in SingleStore DB)
Packed Always NULL (not applicable in SingleStore DB)
Null YES if values in the key column can be NULL, else blank
Index_type The type of index. If index type is BTREE, then index is a SingleStore DB skip list. See Index
Comment Always blank (not used in SingleStore DB)
Index_comment Always blank (not used in SingleStore DB)

Example

memsql> SHOW INDEXES IN mytbl;
    +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
    +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    | mytbl |          1 | seq       |            1 | seq         | NULL      |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
    | mytbl |          1 | seq_index |            1 | seq         | NULL      |        NULL |     NULL | NULL   | YES  | BTREE      |         |               |
    +-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
    2 rows in set (0.00 sec)