MySQL Drop Indexes

Sometimes, you may want to drop one or more indexes from a table for some of the following reasons:

• Created wrong indexes
• To be faster, drop indexes before inserting or updating large amounts of data

MySQL allows you to drop an existing index from a table usding the DROP INDEX statement.

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}
  

  ALGORITHM clause is optional. Default is INSTANT. INSTANT used if not supported INPLACE.

  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 primary key index

In MySQL, the index name of the primary key is fixed as PRIMARY. To drop a primary key index on the table t, use the following statement:

DROP INDEX `PRIMARY` ON t;

Conclusion

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