SQL DROP INDEX Statement

In SQL, we use the CREATE INDEX statement to create an index and retrieve data from a database speedily. So after we have created an index sometimes we need to drop that index because dropping and then recreating the index boosts data insertion speed.

It is necessary to create an index first and only then we can drop that index. In this article, we will learn how to drop an index in SQL using DROP INDEX Statement with examples.

DROP INDEX Statement

The DROP INDEX Command is used to remove an index from the table. Indexes improve the speed of searches/queries but they also occupy space, which can cause extra time consumption on table modification operations.

• Removing unused indexes can improve INSERT, UPDATE, and DELETE operations on the table.
• It will also free up some space.
• Deleting an index can have a significant impact on database queries therefore only drop the unused or unrequired indexes.

Note: Indexes created by PRIMARY KEY or UNIQUE constraint can not be deleted with just a DROP INDEX statement. To delete such indexes, we need to first drop the constraints using the ALTER TABLE statement, and then drop the index.

Syntax

DROP INDEX Statement differs in different database systems, for example

MySQL

ALTER TABLE table_name DROP INDEX index_name;

MS Access

DROP INDEX index_name ON table_name;

SQL Server

DROP INDEX table_name.index_name;

DB2/Oracle

 DROP INDEX index_name;

PostgreSQL

DROP INDEX index_name;

Creating an Index on Table

Before learning how to drop an index we need to create an index. Let’s create a table and add index using the CREATE INDEX Statement. We will be using MySQL database in the examples. 

MySQL

CREATE DATABASE GEEKSFORGEEKS;
USE GEEKSFORGEEKS;
 CREATE TABLE EMPLOYEE(
   EMP_ID INT,
   EMP_NAME VARCHAR(20),
   AGE INT,
   DOB DATE,
   SALARY DECIMAL(7,2)); 
 CREATE INDEX EMP
 ON EMPLOYEE(EMP_ID,EMP_NAME); 

Output:

Creating an index on two columns

SQL DROP INDEX Statement Example

Let’s cover examples of DROP INDEX statement and understand its workings in SQL. We will learn different use cases of the DROP INDEX statement in these examples.

We can drop the index using two ways either with IF EXISTS or with ALTER TABLE so we will first drop the index using if exists.

Example 1: Dropping the index with IF EXISTS

DROP INDEX IF EXISTS EMP ON EMPLOYEE;

Dropping index

Output

Since there are no indexes in the database with the supplied name, the aforementioned query simply ends execution without returning any errors.

Commands Excuted Successfully;

Example 2: Dropping the index with ALTER TABLE in SQL

 ALTER TABLE EMPLOYEE
 DROP INDEX EMP;

Output

Dropping the index

Verifying DROP INDEX Statement

To verify if the DROP INDEX statement has successfully removed the index from the table, we can check the indexes on the table. If the index is not present in the list, we know it has been deleted.

Syntax

The syntax for viewing index on a table differ for different databases, for example:

SQL Server:

SELECT * FROM sys.indexes WHERE object_id = (SELECT object_id FROM sys.objects WHERE name = 'YOUR_TABLE_NAME')

MySQL:

SHOW INDEXES FROM YOUR_TABLE_NAME;

PostgreSQL:

SELECT * FROM USER_INDEXES;

Oracle:

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'your_table_name';

Conclusion

In summary, the SQL DROP INDEX statement is used to remove an index from a database table, providing greater control over index management and potentially improving database performance by reducing index maintenance overhead.

SQL DROP INDEX Statement – FAQs

How to create an index in SQL?

To create an index in MYSQL we use the CREATE INDEX command.

How to drop an index in SQL?

To drop an index in SQL we use the ALTER TABLE DROP INDEX command.

What is the need to drop an index?

Generally we drop an index and then recreate it because it increases the data insertion speed.