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.
- Reduces maintenance overhead and storage use.
- 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
Create a table, add an index using the CREATE INDEX statement and then remove 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 query creates an index named EMP on the EMP_ID and EMP_NAME columns of the EMPLOYEE table to improve query performance.
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
The IF EXISTS clause drops the index only if it exists, preventing errors when the index is not found.
Query:
DROP INDEX IF EXISTS EMP ON EMPLOYEE;Output:
Commands Executed Successfully;Step 4: Verifying the DROP INDEX
To verify whether the index has been successfully deleted, use the following query:
Query:
SHOW INDEXES FROM EMPLOYEE;