SHOW INDEX

Description

This statement is used to display information about indexes in a table. For internal tables, only inverted indexes and ann indexes are supported.

For a table in a Lance Filesystem Catalog, this statement displays the logical scalar and vector indexes recorded in the Lance dataset, and only the Table, Key_name, Seq_in_index, Column_name, Index_type, and Properties columns carry values. Lance REST Catalogs are not supported. See Inspect Lance Indexes for details.

For tables in other external catalogs, such as Iceberg and Paimon, this statement returns an empty result. Those formats expose table metadata through system tables instead; see Iceberg system tables and Paimon system tables. Paimon index files can be inspected through the table_indexes system table.

Syntax

SHOW INDEX [ ES ] FROM [ <db_name>. ] <table_name> [ FROM <db_name> ];

Varaint Syntax

SHOW KEY[ S ] FROM [ <db_name>. ] <table_name> [ FROM <db_name> ];

Required Parameters

1. <table_name>: The name of the table to which the index belongs.

Optional Parameters

1. <db_name>: The database name, optional. If not specified, the current database is used by default.

Return Value

Column Name	Type	Description
Table	string	The name of the table where the index is located.
Non_unique	int	Indicates whether the index is unique: - 0: Unique index - 1: Non-unique index
Key_name	string	The name of the index.
Seq_in_index	int	The position of the column in the index. This column shows the order of the column in the index, used when multiple columns form a composite index.
Column_name	string	The name of the indexed column.
Collation	string	The sorting order of the index column: - A: Ascending - D: Descending.
Cardinality	int	The number of unique values in the index. This value is used to estimate query efficiency; the higher the value, the higher the index selectivity and query efficiency.
Sub_part	int	The prefix length used by the index. If the index column is a string type, Sub_part represents the length of the first few characters of the index.
Packed	string	Whether the index is compressed.
Null	string	Whether NULL values are allowed: - YES: NULL values allowed - NO: NULL values not allowed
Index_type	string	The type of index: - BTREE: B+ tree index (default type in MySQL) - HASH: Hash index - RTREE: R-tree index - INVERTED: Inverted index (e.g., full-text index)
Comment	string	The comment or description of the index, typically custom remarks.
Properties	string	Additional properties of the index.

Access Control Requirements

The user executing this SQL command must have at least the following privileges:

Privilege	Object	Notes
SHOW_PRIV	Database

Examples

• Display indexes for a specified table_name

   SHOW INDEX FROM example_db.table_name;

• Display logical indexes for a Lance Catalog table

   SHOW INDEX FROM lance_catalog.default.items;