MySQL DROP INDEX Statement

The DROP INDEX statement in MySQL is used to remove an existing index from a table. Deleting an unused or unnecessary index helps reduce storage usage and improves the performance of data modification operations such as INSERT, UPDATE, and DELETE.

• Remove indexes that are no longer required.
• Reduce storage space used by indexes.
• Improve the performance of data modification operations.
• Simplify database index management.

Syntax

DROP INDEX index_name
ON table_name;

Where:

• DROP INDEX: Removes an existing index from a table.
• Index Name: The name of the index to be deleted.
• Table Name: The table from which the index is removed.

Working

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(50),
    Department VARCHAR(30)
);

INSERT INTO Students VALUES
(101, 'Alice Johnson', 'Computer Science'),
(102, 'Brian Smith', 'Mathematics'),
(103, 'Charlie Brown', 'Physics');

Create an index before deleting it:

CREATE INDEX idx_student_name
ON Students(StudentName);

Example 1: Drop an Index

The following statement removes the idx_student_name index from the Students table.

Query:

DROP INDEX idx_student_name;
ON Students;

Output:

• The DROP INDEX statement deletes the specified index while keeping the table and its data unchanged.

Example 2: Verify the Index Removal

The following statement displays all indexes on the Students table.

Query:

SHOW INDEX
FROM Students;

Output:

• The output shows that only the PRIMARY index remains because idx_student_name has been removed.