How to Drop Index in MySQL

Today’s post will look at the methods for dropping an index in MySQL.

Drop Index in MySQL

Indexing columns that are often queried speeds up SELECT queries since the index allows MySQL to skip entire table searches. Dropping indices can also be beneficial at times.

MySQL must update any indexes that include the changed columns whenever a record is amended. If you don’t use a certain index frequently, your table may be over-indexed, and deleting the index will improve the efficiency of table updates.

To further understand the previous concept, consider the following syntax:

DROP INDEX index_name ON table_name;
DROP INDEX `PRIMARY` ON table_name;
ALTER TABLE table_name DROP `PRIMARY` KEY;
ALTER TABLE table_name DROP INDEX index_name;
ALTER TABLE table_name DROP INDEX index_name_1, DROP INDEX index_name_2;

Here, index_name is the name of the index you wish to delete, and table_name is the name of the table from which the index should be deleted.

Dropping a primary key is the simplest because you don’t need to know the name of the index. In MySQL, primary keys are always called PRIMARY.

However, because PRIMARY is a reserved term, it must be followed by backticks when used in the DROP INDEX command.

You must mention the index_name when dropping an index that is not a PRIMARY key. Use SHOW INDEX if you don’t know the name.

The last phrase demonstrates how, if you separate the operations with commas, you may conduct numerous drop actions with a single ALTER TABLE query.

DROP INDEX email ON Employees;
ALTER TABLE Employees DROP INDEX email;

We are removing the Employees table’s email index in the above example. This will destroy the index; the email column and data will remain intact.

Run the above code line in any browser compatible with MySQL. It will display the following outcome:

Output:

successfully dropped index