How do I 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 as a percentage. 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. Columnar index: If INDEX_STATE is set to `ACTIVE`, the latest progress of data synchronization is displayed. Data that is written to before than the displayed time has been synchronized to the columnar index. If INDEX_STATE is set to `INACTIVE`, the reason why the index failed to be built is displayed. If INDEX_STATE is set to `BUILDING`, no progress is displayed.
INDEX_TYPE	The index type. Valid values: SECONDARY: The index is a secondary index. SEARCH: The index is a search index. columnar: The index is a columnar index.
INDEX_COVERED	Indicates whether the index contains redundant columns. Secondary index: If the index contains redundant columns. `TRUE` is returned. Otherwise, `FALSE` is returned. Note For more information about redundant columns, see Secondary indexes. Search index: Search indexes do not support redundant columns. Therefore, if the queried index is a search index, `N/A` is returned for the field. Columnar index: Columnar indexes do not support redundant columns. Therefore, if the queried index is a columnar index, `N/A` is returned for the field.
INDEX_COLUMN	The columns in the index.
INDEX_TTL	The TTL for indexed data. Unit: seconds. Important The TTL for indexed data cannot be configured for columnar indexes. Therefore, if the queried index is a columnar index, the value of this field is empty. 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. Columnar index: The location of the columnar index table, synchronization task ID, partition information, and user attributes are returned for this field.

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);

-- Create a columnar index named idx3.
CREATE INDEX IF NOT EXISTS idx3 USING COLUMNAR
ON test(p1, p2, c1, c2, c3)
PARTITION BY ENUMERABLE (p1, p2, bucket(128, p1, p2));

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         |                                       |
| hd           | test       | idx3       | ACTIVE      | 2023-11-27 17:02:03.602 +0800 | COLUMNAR   | NA            | p1,p2,c1,c2,c3 |           | index table:                          |
|              |            |            |             |                               |            |               |                |           | __columnar_index_db__.hd_test_idx3;   |
|              |            |            |             |                               |            |               |                |           | task id:                              |
|              |            |            |             |                               |            |               |                |           | 7fcc****-46**-4f**-90**-07344ced****; |
|              |            |            |             |                               |            |               |                |           | partition by:                         |
|              |            |            |             |                               |            |               |                |           | [p1,p2,hash(128,p1,p2)]; attributes:  |
|              |            |            |             |                               |            |               |                |           | []                                    |
+--------------+------------+------------+-------------+-------------------------------+------------+---------------+----------------+-----------+---------------------------------------+