You can use the SHOW INDEX syntax to view the information about the indexes of the specified table, including the index type, index building progress, and index columns. This way, you can quickly query the index schema of the specified table. You can also use the SHOW INDEX syntax before you delete a secondary index or search index to check whether the index is disabled.
Applicable engines
The SHOW INDEX syntax is applicable only to LindormTable.
Syntax
show_index_statement ::= SHOW INDEX FROM table_identifier; Returned results
Field | Description |
TABLE_SCHEMA | The database that stores the queried table. |
DATA_TABLE | The name of the queried table. |
INDEX_NAME | The name of the index of the queried table. |
INDEX_STATE | The state of the index. Valid values:
|
INDEX_PROGRESS | The building progress of the index. The displayed building progress varies with the type of the index.
|
INDEX_TYPE | The index type. Valid values:
|
INDEX_COVERED | Indicates whether the index contains included columns.
|
INDEX_COLUMN | The columns in the index. |
INDEX_TTL | The TTL for indexed data. Unit: seconds. Important If the TTL for indexed data is not configured, this field is empty for a secondary index and is 0 for a search index. |
INDEX_DESCRIPTION | Additional information about the index.
|
Examples
In the following example, the base table and the index are created by executing the following statements:
-- Create the primary table
CREATE TABLE test (
p1 VARCHAR NOT NULL,
p2 INTEGER NOT NULL,
c1 BIGINT,
c2 DOUBLE,
c3 VARCHAR,
c4 TIMESTAMP,
PRIMARY KEY(p1, p2)
) WITH (CONSISTENCY = 'strong', MUTABILITY='MUTABLE_LATEST');
-- Create secondary index idx1
CREATE INDEX idx1 ON test(c1 desc) include(c3,c4) WITH (COMPRESSION='ZSTD');
-- Create search index idx2
CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test(p1, p2, c1, c2, c3);Execute the following statement to view all indexes of the test table:
SHOW INDEX FROM test;The following result is returned:
+--------------+------------+------------+-------------+-------------------------------+------------+---------------+----------------+-----------+---------------------------------------+
| TABLE_SCHEMA | DATA_TABLE | INDEX_NAME | INDEX_STATE | INDEX_PROGRESS | INDEX_TYPE | INDEX_COVERED | INDEX_COLUMN | INDEX_TTL | INDEX_DESCRIPTION |
+--------------+------------+------------+-------------+-------------------------------+------------+---------------+----------------+-----------+---------------------------------------+
| hd | test | idx1 | ACTIVE | 100% | SECONDARY | TRUE | c1,p1,p2 | | |
| hd | test | idx2 | ACTIVE | N/A | SEARCH | NA | p1,c3,p2,c2,c1 | 0 | |
+--------------+------------+------------+-------------+-------------------------------+------------+---------------+----------------+-----------+---------------------------------------+