SHOW INDEX, SHOW INDEXES, SHOW KEYS

Displays the list of indexes associated with a given table.

Syntax

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

Remarks

  • tbl_name - name of a table in a SingleStore database.

  • db_name - name of a SingleStore database. If not specified, then the currently open database is used.

  • expr - SQL WHERE expression.

  • The Key_name for shard keys on columnstore tables is listed as "__SHARDKEY" in the SHOW INDEX, SHOW INDEXES, or SHOW KEYS output.

  • The Index_type for shard keys on columnstore tables is listed at "METADATA_ONLY" in the SHOW INDEX, SHOW INDEXES, or SHOW KEYS output.

  • This command must be run on the master aggregator or a child aggregator node (see Node Requirements for SingleStore Commands).

Output

Column

Description

Table

Table name

Non_unique

Non-unique

Key_name

Key name

Seq_in_index

Sequence in the index

Column_name

Column name

Collation

Collation

Cardinality

Cardinality

Sub_part

Sub-part

Packed

Packed

Null

Null

Index_type

Index type (see Index)

Comment

Comment

Index_comment

Comment

Example

SHOW INDEX 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      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
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      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
SHOW KEYS 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      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
SHOW INDEXES in my_columnstore_tbl;
+--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+---------------+----------+---------------+
| Table              | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type    | Comment  | Index_comment | 
+--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+---------------+----------+---------------+
| my_columnstore_tbl |          1 | __SHARDKEY |            1 | a           | NULL      |        NULL |     NULL | NULL   | YES  | METADATA_ONLY |          |               |
+--------------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+---------------+----------+---------------+

Last modified: December 14, 2023

Was this article helpful?