Outdated Version

You are viewing an older version of this section. View current production version.

DROP INDEX

Drop the specified index on the specified table.

Syntax

DROP INDEX index_name ON tbl_name

Remarks

  • index_name is the name of the index you want to drop.

  • tbl_name is the name of a table in a MemSQL database.

  • This command must be run on the master aggregator node (see Node Requirements for MemSQL Commands ).

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

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

  • MemSQL 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 completion of long-running queries.

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

Example

DROP INDEX my_index ON my_tbl;