Drop indexes

An index can be dropped (removed) using the name with the DROP INDEX index_name command. This command can drop indexes of any type, except those backing constraints. The name of the index can be found using the SHOW INDEXES command, given in the output column name.

DROP INDEX index_name [IF EXISTS]

The DROP INDEX command is optionally idempotent. This means that its default behavior is to throw an error if an attempt is made to drop the same index twice. With IF EXISTS, no error is thrown and nothing happens should the index not exist. Instead, an informational notification is returned detailing that the index does not exist.

Dropping an index requires the DROP INDEX privilege.

Examples

Drop an index

The following statement attempts to drop the index named example_index.

Dropping an index
DROP INDEX example_index

If an index with that name exists it is removed, if not the command fails.

Drop an index using a parameter

The following statement attempts to drop the index named range_index_param using a parameter for the index name.

Parameters
{
  "name": "range_index_param"
}
Dropping an index
DROP INDEX $name

If an index with that name exists it is removed, if not the command fails.

Failure to drop an index backing a constraint

It is not possible to drop indexes that back constraints.

Dropping an index backing a constraint
DROP INDEX uniqueBookIsbn
Error message
Unable to drop index: Index belongs to constraint: `uniqueBookIsbn`

Dropping the index-backed constraint also removes the backing index. For more information, see Drop a constraint by name.

Drop a non-existing index

If it is uncertain if an index exists and you want to drop it if it does but not get an error should it not, use IF EXISTS.

The following statement attempts to drop the index named missing_index_name.

Dropping an index with IF EXISTS
DROP INDEX missing_index_name IF EXISTS

If an index with that name exists it is removed, if not the command does nothing and an informational notification is instead returned.

Notification
`DROP INDEX missing_index_name IF EXISTS` has no effect. `missing_index_name` does not exist.