Retrieve LindormTable Index Status via SHOW INDEX - Lindorm

SHOW INDEX returns all indexes defined on a table, including each index's type, build state, progress, and column composition. Run this statement before dropping or disabling an index to confirm its current state.

SHOW INDEX is supported only on LindormTable.

Syntax

SHOW INDEX FROM table_identifier;

Returned fields

Field	Description
`TABLE_SCHEMA`	The database that contains the queried table.
`DATA_TABLE`	The name of the queried table.
`INDEX_NAME`	The name of the index.
`INDEX_STATE`	The build state of the index. Possible values: `BUILDING` (build in progress), `ACTIVE` (build complete for existing data; incrementally tracking new writes), `INACTIVE` (build failed), `DISABLED` (index is disabled).
`INDEX_PROGRESS`	The build progress. The format differs by index type — see INDEX_PROGRESS details below.
`INDEX_TYPE`	The index type. Possible values: `SECONDARY` (secondary index), `SEARCH` (search index).
`INDEX_COVERED`	Whether the index contains included columns. For secondary indexes: `TRUE` if included columns exist, `FALSE` otherwise. For search indexes: `NA` (search indexes do not support included columns).
`INDEX_COLUMN`	The columns in the index.
`INDEX_TTL`	The TTL for indexed data, in seconds. If no TTL is set: empty for secondary indexes, `0` for search indexes.
`INDEX_DESCRIPTION`	Additional information about the index. Currently empty for both secondary and search indexes.

INDEX_PROGRESS details

Secondary indexes — progress is reported by stage:

Stage	Meaning
`NOT_STARTED`	The build task has not been submitted.
`WAITING`	The task is submitted and waiting to run.
`EXECUTING`	The task is running. Progress is shown as a percentage in `xx.xx%` format.
`FAILED`	The task failed. The first 256 characters of the error message are shown.
`CANCELLED`	The task was canceled.

Important

Secondary indexes require LindormTable 2.6.7 or later. To check or upgrade your version, see Release notes of LindormTable and Upgrade the minor engine version of a Lindorm instance. If upgrading is unavailable in the console, contact technical support on DingTalk (ID: s0s3eg3).

Search indexes — real-time build progress is not available, so INDEX_PROGRESS always shows N/A. To check the actual replication progress, go to the LTS console and choose Lindorm Search > Full Data replication to Search, then check the State field.

Example

The following example creates a base table with one secondary index (idx1) and one search index (idx2), then runs SHOW INDEX.

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

-- Create the search index idx2
CREATE INDEX IF NOT EXISTS idx2 USING SEARCH ON test(p1, p2, c1, c2, c3);

Query all indexes on the test table:

SHOW INDEX FROM test;

Output:

+--------------+------------+------------+-------------+-------------------------------+------------+---------------+----------------+-----------+---------------------------------------+
| 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         |                                       |
+--------------+------------+------------+-------------+-------------------------------+------------+---------------+----------------+-----------+---------------------------------------+

idx1 is a secondary index with included columns (c3, c4), so INDEX_COVERED is TRUE. idx2 is a search index, which does not support included columns, so INDEX_COVERED is NA. For more information about included columns, see Secondary indexes.