MySQL DROP INDEX: Syntax and Examples

Drop an index when it is incorrect, redundant, or no longer helps the workload. Before removing one, check its definition and confirm that queries no longer depend on it; every index affects both storage and write cost.

MySQL removes a secondary index with DROP INDEX or the equivalent ALTER TABLE ... DROP INDEX operation. For InnoDB, dropping a secondary index is an in-place operation that permits concurrent DML; it is not an instant operation. The exact algorithm and lock support depend on the server version and storage engine. See the MySQL 9.7 DROP INDEX reference and InnoDB online DDL index matrix.

MySQL DROP INDEX statement syntax

You should drop an index as the following syntax of DROP INDEX:

DROP INDEX index_name
ON table_name
[algorithm_option | lock_option];

In this syntax:

• index_name is the name of the index to be dropped.

• table_name is the name of the table.

• algorithm_option specifies the algorithm for dropping indexes. It uses the following syntax:

  ALGORITHM [=] {DEFAULT | INPLACE | COPY}
  

  In MySQL 9.7, the ALGORITHM clause accepts DEFAULT, INPLACE, or COPY; INSTANT is not a DROP INDEX algorithm. If you omit the clause or use DEFAULT, MySQL selects a supported algorithm for the operation and storage engine. For InnoDB secondary indexes, dropping is supported in place. If an operation cannot use INPLACE, MySQL can use COPY. Check the support for your engine and server version before requiring a specific algorithm or lock level.

  Using DEFAULT and omitting the ALGORITHM clause has the same effect.

  The following is a description of each algorithm:

  • COPY: Operate on the copy of the original table, and copy the table data in the original table to the new table row by row. Concurrent DML is not allowed.
  • INPLACE: The operation avoids copying table data, but may rebuild the table in-place. Exclusive metadata locks on tables may be briefly held during the preparation and execution phases of an operation. In general, concurrent DML is supported.

• lock_option Specifies the concurrency control strategy for dropping indexes. It uses the following syntax:

  LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
  

  LOCK clause is optional. The following are descriptions of each concurrency strategy:

  DEFAULT

  The maximum concurrency level for the given ALGORITHM clause (if any) and ALTER TABLE operation: Concurrent reads and writes are allowed, if supported. If not, concurrent reads are allowed (if supported). If not, exclusive access is enforced.

  NONE

  Allows concurrent reads and writes if supported. Otherwise, an error will occur.

  SHARED

  If supported, allow concurrent reads but block writes. Writes are blocked even if the storage engine supports concurrent writes for the given ALGORITHM clause (if any) and operation. ALTER TABLE An error occurs if concurrent reads are not supported.

  EXCLUSIVE

  Enforce exclusive access. This is done even if the storage engine supports concurrent read/write for the given ALGORITHM clause (if any) and operation.ALTER TABLE

Internally in MySQL, the DROP INDEX statement is mapped to the ALTER TABLE ... DROP INDEX ... statement.

MySQL DROP INDEX Examples

In the our MySQL creating index tutorial, we created an index named first_name in the actor table from the Sakila sample database .

Now, we will drop it using the following statement:

DROP INDEX first_name ON actor;

To see whether the index was dropped successfully, use the following SHOW INDEXES statement display all the index of the actor table, for example:

SHOW INDEXES FROM actor;

Here’s the output:

+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name            | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| actor |          0 | PRIMARY             |            1 | actor_id    | A         |         201 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| actor |          1 | idx_actor_last_name |            1 | last_name   | A         |         122 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

Drop a primary key

The primary-key index is named PRIMARY. You can drop it with either form:

ALTER TABLE t DROP PRIMARY KEY;

DROP INDEX `PRIMARY` ON t;

For InnoDB, dropping a primary key without adding another requires the COPY algorithm and rebuilds the table. Plan for the table copy and its write impact before running this operation. See MySQL’s primary-key DDL notes.

Conclusion

In MySQL, you can use DROP INDEX to drop a specified index from a table.