SHOW INDEX, SHOW INDEXES, SHOW KEYS
On this page
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 of a table in a SingleStore database.name -
db_- name of a SingleStore database.name If not specified, then the currently open database is used. -
expr- SQL WHERE expression. -
The
Key_for shard keys on columnstore tables is listed as "_name _ SHARDKEY" in the SHOW INDEX,SHOW INDEXES, orSHOW KEYSoutput. -
The
Index_for shard keys on columnstore tables is listed at "METADATA_type ONLY" in the SHOW INDEX,SHOW INDEXES, orSHOW KEYSoutput. -
This command must be run on the master aggregator or a child aggregator node (see Node Requirements for SingleStore Commands).
Output
|
Column |
Description |
|---|---|
|
|
Table name |
|
|
Non-unique |
|
|
Key name |
|
|
Sequence in the index |
|
|
Column name |
|
|
Collation |
|
|
Cardinality |
|
|
Sub-part |
|
|
Packed |
|
|
Null |
|
|
Index type (see 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