SQL DROP INDEX Statement

Indexes improve query performance by speeding up data retrieval in SQL databases. However, they add overhead to INSERT, UPDATE, and DELETE operations. When an index becomes unnecessary, it can be removed using the DROP INDEX statement to improve overall performance.

• Removes an existing index from a table.
• Reduces maintenance overhead and storage use.
• Improves INSERT, UPDATE, and DELETE speed.
• PRIMARY KEY / UNIQUE indexes need constraint removal first.
• IF EXISTS avoids errors when index is missing.

Syntax:

DROP INDEX index_name ON table_name;

Example of SQL DROP INDEX

Let’s go through an example where we create a table and add an index using the CREATE INDEX statement, and then drop it using the DROP INDEX statement.

Step 1: Create the EMPLOYEE Table

CREATE TABLE EMPLOYEE(
   EMP_ID INT,
   EMP_NAME VARCHAR(20),
   AGE INT,
   DOB DATE,
   SALARY DECIMAL(7,2)); 

Step 2: Create an Index on the EMPLOYEE Table

CREATE INDEX EMP
ON EMPLOYEE(EMP_ID, EMP_NAME);

This creates an index named EMP on the EMP_ID and EMP_NAME columns of the EMPLOYEE table, improving query performance for queries involving these columns.

Step 3: Dropping an Index

Now let's look at some examples of DROP INDEX statement and understand its workings in SQL.

Example: SQL DROP INDEX with IF EXISTS

Using the IF EXISTS clause ensures that the index is dropped only if it already exists in the table. This prevents errors from being thrown if the index is not present.

Query:

DROP INDEX IF EXISTS EMP ON EMPLOYEE;

Output

Commands Executed Successfully;

Explanation:

This query drops the EMP index from the EMPLOYEE table only if the index exists, ensuring no error occurs if the index is absent. Since the EMP index exists, it is successfully removed. If the index didn’t exist, no error would be thrown.

Step 4: Verifying the DROP INDEX

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. This can be done using database-specific commands.

Query:

SHOW INDEXES FROM EMPLOYEE;

Explanation:

These queries retrieve information about the indexes associated with the EMPLOYEE table. If the index was successfully removed, it will not appear in the results.