Remove LindormTable Indexes Safely with DROP INDEX - Lindorm

Deletes an existing index from a LindormTable table.

Applicable engines and versions

DROP INDEX applies to all versions of LindormTable.

Important

To drop a search index or columnar index, the Lindorm SQL version must be 2.6.1 or later. To check your Lindorm SQL version, see SQL versions.

Syntax

DROP INDEX [IF EXISTS] index_identifier ON table_identifier;

Parameters

Parameter	Description
`IF EXISTS`	Optional. If the index exists, it is dropped. If the index does not exist, the statement is a no-op and returns no error. Without `IF EXISTS`, the statement returns an error if the index does not exist.
`index_identifier`	The name of the index to drop.
`table_identifier`	The name of the table that the index belongs to.

Usage notes

Before dropping a secondary index or search index, disable the index with ALTER INDEX ... DISABLED, then confirm it is in the INACTIVE state using SHOW INDEX. For details, see ALTER INDEX and SHOW INDEX.
Dropping a table also drops all indexes on that table.

Examples

The following examples use this base table and its indexes:

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

Drop a secondary index

Disable the index.

ALTER INDEX IF EXISTS idx1 ON test DISABLED;

Drop the index.
```
DROP INDEX IF EXISTS idx1 ON test;
```

Verify the result

Run the following statement to confirm the index has been removed:

SHOW INDEX FROM test;

Drop a search index

Disable the index.

ALTER INDEX IF EXISTS idx2 ON test DISABLED;

Drop the index.
```
DROP INDEX IF EXISTS idx2 ON test;
```

Verify the result

Run the following statement to confirm the index has been removed:

SHOW INDEX FROM test;

What's next

ALTER INDEX — disable or rebuild an index
SHOW INDEX — list indexes on a table and check their status
CREATE INDEX — create a new index on a table