MySQL Index: How To Drop Index in MySQL

In this tutorial, we will learn how to drop an index from a table in MySQL and we’ll see a few examples related to it as well. But before that, let’s have a quick look at what Indexes are and how to create one.

Indexes are data structures in SQL to store fields or columns in such a way that the accessing of elements or result sets is fast and enhances the overall performance. In MySQL, the primary key that exists in a table is taken as an index by default. We can create indexes for a table on our own as well.

To create an index we can use the below command:

--While creating a table

CREATE TABLE table_name(
field1 datatype,
field2 datatype,
field3 datatype,
field4 datatype,
INDEX (field1,field3);


--If the table already exists

CREATE INDEX index_name ON table_name(field_name);Code language: SQL (Structured Query Language) (sql)

MySQL Drop Index

There might be moments when you don’t want an index, for example, the index you created earlier is no longer required or the performance improvement expected after creating an index is not up to the mark, and other related reasons. In cases like these, we must drop the index.

The syntax for dropping an index:

DROP INDEX index_name ON table_name;Code language: SQL (Structured Query Language) (sql)

We must specify the name of the table, as MySQL allows indexes with the same name to be used in multiple tables.

ALTER TABLE command can also be used for dropping index in MySQL. Let’s see the syntax for the same:

ALTER TABLE table_name DROP INDEX index_name;Code language: SQL (Structured Query Language) (sql)

MySQL Drop Primary Index

As we have already concluded in the introduction of this tutorial, the Primary key of any table by default is taken as an index in MySQL. Therefore, in this section we will see how to drop a primary index, there are only a few minute changes while dropping a primary index.

The syntax for dropping a primary index:

DROP INDEX 'PRIMARY' ON table_name;Code language: SQL (Structured Query Language) (sql)

A Primary index can also be dropped by using ALTER TABLE command, let’s see how:

ALTER TABLE table_name DROP PRIMARY KEY;Code language: SQL (Structured Query Language) (sql)

Examples of Drop index

In this section, we’ll be going through a few examples of drop indexes. Let’s have a table name students that has fields like- first name, last name, standard, section, contact number, age, and class teacher. We already have an index present in the table as index1 and the columns are first name, last name, and contact number. Now the index is no longer required, let’s see how we’ll remove this index.

--You can use DROP command
DROP INDEX index1 ON students;

--You can use ALTER TABLE command
ALTER TABLE students DROP INDEX index1;Code language: SQL (Structured Query Language) (sql)

Let’s take another example, suppose we have an employee table, that has fields such as – employee id, first name, last name, DOJ, contact details, address, department, manager, and salary. Now this table contains the primary key which is the employee id. What we need to do is to drop the primary key.

Let’s see the command to drop the primary key:

--Using DROP command
DROP INDEX 'PRIMARY' ON employee;

--Using ALTER TABLE command
ALTER TABLE employee DROP PRIMARY KEY;Code language: JavaScript (javascript)

Conclusion

In this tutorial, we learned the DROP INDEX command, and we saw the syntax for the DROP INDEX and ALTER TABLE DROP commands. We learned how to implement it with the help of a few examples.

SEE ALSO: Official Documentation for more information on the DROP command.