Query indexes of a table-Lindorm(Lindorm)-阿里云帮助中心

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 running. The real-time progress 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 progress is indicated by the following states. DONE: The task is complete. WAITING: The task has been submitted and is awaiting execution. EXECUTING: The task is running. The real-time progress is displayed as a percentage in the `xx.xx%` format. FAILED: The task failed. The error message is displayed in the INDEX_DESCRIPTION field. Only the first 256 characters of the message are shown. Note The build progress for a search index is displayed only if you are using LindormTable 2.8.5 or later and LTS 3.8.13.5 or later. For earlier versions, or to view more detailed progress and error information, go to the LTS console. In the left-side navigation pane, choose Lindorm Search > Full Data replication to Search and view the value of the State parameter and other task details. To view or upgrade your current version, see Upgrade the minor engine version of a Lindorm instance. If you cannot upgrade to a supported version in the console, contact Lindorm technical support (DingTalk ID: s0s3eg3). columnar index: If INDEX_STATE is `ACTIVE`, this field displays a timestamp. All data written to the base table before this time is synchronized to the columnar index. This timestamp reflects the write time of the data in the base table. If INDEX_STATE is `INACTIVE`, this field displays the reason for the build failure. If INDEX_STATE is `BUILDING`, this field is empty.
INDEX_TYPE	The index type. Valid values: SECONDARY: The index is a secondary index. SEARCH: The index is a search index. COLUMNAR: A columnar index.
INDEX_COVERED	Indicates whether the index contains included columns. secondary index: Returns `TRUE` if the index has included columns, and `FALSE` otherwise. Note For more information about included columns, see Secondary indexes. search index: Always returns `NA`. This index type does not support included columns. columnar index: Always returns `NA`. This index type does not support included columns.
INDEX_COLUMN	The columns in the index.
INDEX_TTL	The TTL for indexed data. Unit: seconds. Important A TTL cannot be set for a columnar index. Therefore, the INDEX_TTL field is always empty. If no TTL is set, the INDEX_TTL field is empty for a secondary index and returns 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: Contains details such as the location of the columnar index table, synchronization task ID, partition information, and user attributes.

Examples

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

-- Create a 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:  |
|              |            |            |             |                               |            |               |                |           | []                                    |
+--------------+------------+------------+-------------+-------------------------------+------------+---------------+----------------+-----------+---------------------------------------+