MySQL Drop Index

MySQL allows a DROP INDEX statement to remove the existing index from the table. To delete an index from a table, we can use the following query:

mysql>DROP INDEX index_name ON table_name [algorithm_option | lock_option];

If we want to delete an index, it requires two things:

• First, we have to specify the name of the index that we want to remove.
• Second, name of the table from which your index belongs.

The Drop Index syntax contains two optional options, which are Algorithm and Lock for reading and writing the tables during the index modifications. Let us explain both in detail:

Algorithm Option

The algorithm_option enables us to specify the specific algorithm for removing the index in a table. The syntax of algorithm_option are as follows:

Algorithm [=] {DEFAULT | INPLACE | COPY}  

The Drop Index syntax supports mainly two algorithms which are INPLACE and COPY.

COPY: This algorithm allows us to copy one table into another new table row by row and then DROP Index statement performed on this new table. On this table, we cannot perform an INSERT and UPDATE statement for data manipulation.

INPLACE: This algorithm allows us to rebuild a table instead of copy the original table. We can perform all data manipulation operations on this table. On this table, MySQL issues an exclusive metadata lock during the index removal.

Note: If you not defined the algorithm clause, MySQL uses the INPLACE algorithm. If INPLACE is not supported, it uses the COPY algorithm. The DEFAULT algorithm works the same as without using any algorithm clause with the Drop index statement.

Lock Option

This clause enables us to control the level of concurrent reads and writes during the index removal. The syntax of lock_option are as follows:

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

In the syntax, we can see that the lock_option contains four modes that are DEFAULT, NONE, SHARED, and EXCLUSIVE. Now, we are going to discuss all the modes in detail:

SHARED: This mode supports only concurrent reads, not concurrent writes. When the concurrent reads are not supported, it gives an error.

DEFAULT: This mode can have the maximum level of concurrency for a specified algorithm. It will enable concurrent reads and writes if supported otherwise enforces exclusive mode.

NONE: You have concurrent read and write if this mode is supported. Otherwise, it gives an error.

EXCLUSIVE: This mode enforces exclusive access.

Example

First, execute the following command to show the indexes available in the table.

mysql> SHOW INDEXES FROM student; 

It will give the following output.

In the output, we can see that there are three indexes available. Now, execute the following statement to removes the class index from table student.

mysql> DROP INDEX class ON student;  

Again, execute the SHOW INDEXES statement to verify the index is removed or not. After performing this statement, we will get the following output, where only two indexes are available.

Example using Algorithm and Lock

The following statement drops the age index form the student table using an algorithm and a lock option.

mysql> DROP INDEX age ON student ALGORITHM = INPLACE LOCK = DEFAULT;  

MySQL Drop PRIMARY Key Index

In some cases, the table contains a PRIMARY index that was created whenever you create a table with a primary key or unique key. In that case, we need to execute the following command because the PRIMARY is a reserved word.

mysql> DROP INDEX PRIMARY ON table_name;  

To remove the primary key index from the student table, execute the following statement:

mysql> DROP INDEX PRIMARY ON student;