Query the indexes of a table - Lindorm - Alibaba Cloud Documentation Center

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: BUILDING: The index is being built. ACTIVE: The index is built for existing data and is being built for incremental data. INACTIVE: The index failed to be built. DISABLED: The index is disabled.
INDEX_PROGRESS	The building progress of the index. The displayed building progress varies with the type of the index. Secondary index: The building progress of a secondary index is displayed by stage. A secondary index building task can be in one of the following stages: NOT_STARTED: The task is not submitted. WAITING: The task has been submitted and is pending to be executed. EXECUTING: The task is being executed. The progress of the task in this stage is displayed as a percentage in the `xx.xx%` format. FAILED: The task failed. In this case, the first 256 characters of the returned error message is displayed. CANCELLED: The task has been canceled. Important Secondary indexes are supported only in LindormTable 2.6.7 and later versions. For more information about how to view or upgrade the version of LindormTable, see Release notes of LindormTable and Upgrade the minor engine version of a Lindorm instance. If you cannot upgrade the LindormTable version of your Lindorm instance to 2.6.7 or later in the console, contact the technical support (DingTalk ID: s0s3eg3). Search index: The actual building progress of a search index cannot be displayed. Therefore, the building progress of a search index is displayed as `N/A`. Note You can perform the following steps to view the building progress of a search index: In the left-side navigation pane of the LTS console, choose Lindorm Search > Full Data replication to Search. On the page that appears, view the value of the State field.
INDEX_TYPE	The index type. Valid values: SECONDARY: The index is a secondary index. SEARCH: The index is a search index.
INDEX_COVERED	Indicates whether the index contains included columns. Secondary index: If the index contains included columns. `TRUE` is returned. Otherwise, `FALSE` is returned. Note For more information about included columns, see Secondary indexes. Search index: Search indexes do not support included columns. Therefore, if the queried index is a search index, `NA` is returned for the field.
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. Secondary index: Secondary indexes do not have additional information. The value of this field is empty. Search index: Search indexes do not have additional information. The value of this field is empty.

Examples

In the following example, the base table and the index are created by executing the following statements:

-- Create the base 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 a secondary index named idx1.
CREATE INDEX idx1 ON test(c1 desc) include(c3,c4) WITH (COMPRESSION='ZSTD');

-- Create a search index named 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         |                                       |
+--------------+------------+------------+-------------+-------------------------------+------------+---------------+----------------+-----------+---------------------------------------+