You are viewing an older version of this section. View current production version.
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 MemSQL database.
  • db_name - name of a MemSQL database.
  • expr - SQL WHERE expression.
  • This command must be run on the master aggregator or a child aggregator node (see Node Requirements for MemSQL 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 MemSQL)
Sub_part Always NULL (not applicable in MemSQL)
Packed Always NULL (not applicable in MemSQL)
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 MemSQL skip list. See Index
Comment Always blank (not used in MemSQL)
Index_comment Always blank (not used in MemSQL)

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)