DROP INDEX

Syntax

DROP INDEX <key_name | index_name> ON <table_name>;

Remarks

• <key_name | index_name> is the name of the index you want to drop.

• <table_name> is the name of a table in a SingleStore database.

• This command can be run on any SingleStore node (see Node Requirements for SingleStore Commands ).

• This command will attempt to run as an online operation, but in certain cases cannot. See ALTER TABLE for more information. Operations that be run offline cannot be run on distributed tables.

• SORT KEY indexes and FULLTEXT indexes cannot be dropped using DROP INDEX. If a table having these indexes is dropped, the indexes are deleted automatically.

  Note

  KEY() USING CLUSTERED COLUMNSTORE is a legacy syntax that is equivalent to SORT KEY(). SingleStore recommends using SORT KEY().

• SingleStore supports online DROP INDEX, which means that you can read and write while the index is being dropped on a table. DROP INDEX on a sharded table is always executed online. Note that online DROP INDEX will not begin dropping the index on the table, but it will wait until all DML queries that were already running on the table finish. This allows any in-progress queries to complete execution before dropping the index on the table, and ensures consistency of results from queries on the table since the time of execution of DROP INDEX. As soon as the in-progress reads and writes complete and the DROP INDEX command begins dropping the index on the table, new reads and writes will proceed as normal. This blocking period usually lasts approximately for milliseconds.

  If you are running frequent DROP INDEX statements on a table and have a lot of long-running queries on that table, then your normal workload may experience some periods of delay since it blocks other queries from starting while it waits for the completion of long-running queries.

  Refer to the Query Errors topic for resolving query timeout errors due to long-running queries in a workload.

• Refer to the Permission Matrix for the required permission.

Examples

CREATE TABLE drop_ind_1 (a INT, b INT, c INT, INDEX ind_1 (b));

DROP INDEX ind_1 ON drop_ind_1;

CREATE TABLE drop_ind_2 (a INT, b INT, c INT, INDEX (b));

DROP INDEX b ON drop_ind_2;

CREATE TABLE drop_ind_3 (a INT, b INT, c INT, INDEX (a,b));

SHOW INDEX FROM drop_ind_3;

+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------------+---------+---------------+---------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type       | Comment | Index_comment | Index_options |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------------+---------+---------------+---------------+
| drop_ind_3 |          1 | a        |            1 | a           | NULL      |        NULL |     NULL |   NULL | YES  | COLUMNSTORE HASH |         |               |               |
| drop_ind_3 |          1 | a        |            2 | b           | NULL      |        NULL |     NULL |   NULL | YES  | COLUMNSTORE HASH |         |               |               |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------------+---------+---------------+---------------+

DROP INDEX a ON drop_ind_3;