DROP INDEX
In this page:
Removing an Index
DROP INDEX permanently removes an index you no longer need, freeing up the storage space it was using and reducing the overhead MySQL pays on every insert or update to that table.
Example: Removing an Index
CREATE TABLE users (id INT, email TEXT);
CREATE INDEX idx_email ON users (email);
DROP INDEX idx_email ON users;Dropping Indexes with ALTER TABLE
Indexes can also be dropped through ALTER TABLE using its DROP INDEX clause, which is handy when you're already running other structural changes on the table in the same statement.
Example: Dropping Indexes with ALTER TABLE
CREATE TABLE users (id INT, email TEXT);
CREATE INDEX idx_email ON users (email);
ALTER TABLE users DROP INDEX idx_email;Why We Drop Indexes
Every index has a cost: MySQL has to update it on every insert, update, or delete against the indexed column, so removing indexes that queries never actually use can noticeably speed up write-heavy tables.
Example: Why We Drop Indexes
CREATE TABLE logs (id INT, note TEXT);
CREATE INDEX idx_note ON logs (note);
-- Every INSERT now also updates idx_note -- drop it if queries never filter on note
DROP INDEX idx_note ON logs;Removing Primary Keys
The index automatically created behind a primary key can't be dropped with a plain DROP INDEX — you remove it specifically with ALTER TABLE ... DROP PRIMARY KEY instead.
Example: Removing Primary Keys
CREATE TABLE users (id INT PRIMARY KEY, email TEXT);
ALTER TABLE users DROP PRIMARY KEY;Viewing Indexes Before Dropping
Before dropping an index, it's worth listing the indexes currently defined on a table so you can confirm the exact name you're targeting rather than guessing and risking removing the wrong one.
Example: Viewing Indexes Before Dropping
CREATE TABLE users (id INT, email TEXT);
CREATE INDEX idx_email ON users (email);
SHOW INDEX FROM users;Chapter Quiz — Complete all 5 topics to unlock
0/5 topics done
Complete these topics first: