/    /  MySQL – Drop Indexes

MySQL – Drop Indexes


A DROP INDEX statement can be used to remove an existing index from a table. The following query can be used to delete an index from a table:

DROP INDEX index_name ON table_name [algorithm_option | lock_option];  

In order to delete an index, we must do two things:

  • In order to remove an index, we must first specify its name.
  • Second, the name of the table from which your index is derived.

Drop Index syntax offers two optional options, Algorithm and Lock, for reading and writing the tables during the index modification process. Here is a more detailed explanation of each:

Algorithm Option

By specifying the algorithm_option, we are able to specify the specific algorithm that will be used to remove the index from a table. The syntax of algorithm_option is as follows:

Algorithm [=] {DEFAULT | INPLACE | COPY}  

Drop Index syntax supports two main algorithms, INPLACE and COPY.

COPY: By using this algorithm, we can copy a table row by row into a new table, and then DROP Index statements can be applied to this new table. Data manipulation cannot be performed on this table by using an INSERT and UPDATE statement.

INPLACE: The algorithm allows us to rebuild a table instead of copying the original table. On this table, we are able to perform all data manipulation operations. MySQL issues an exclusive metadata lock during the removal of the index on this table.

Lock Option

By using this clause, we will be able to control the level of concurrent reads and writes during the index removal process. Lock_option has the following syntax:


Based on the syntax, we can see that the lock_option has four modes: DEFAULT, NONE, SHARED, and EXCLUSIVE. We will now discuss all the modes in more detail:

SHARED: Only concurrent reads are supported in this mode, not concurrent writes. An error is displayed when concurrent reads are not supported.

DEFAULT: In this mode, the maximum level of concurrency can be achieved for a given algorithm. If supported, concurrent reading and writing will be permitted. Otherwise, exclusive mode will be enforced.


As a first step, run the following command to view the available indexes in the table.

SHOW INDEXES FROM employee   IN Demo;

MySQL - Drop Indexes

The output indicates that there are two indexes available. To remove the class index from the employee table, execute the following statement.

DROP INDEX role ON employee;

To verify whether the index has been removed, execute the SHOW INDEXES statement once again. As a result of this statement, we will receive the following output, which contains only two indexes.