MySQL Drop Index

Last updated: October 19, 2026Oct 19, 2026 4 min read

Indexes speed up reads but slow down every write and take space. An index nobody uses, or one that duplicates another, is pure cost. DROP INDEX removes it. This tutorial shows the syntax, the special cases (primary keys, unique keys, foreign keys) and a safe way to decide what to drop.

1. Syntax

DROP INDEX index_name ON table_name [ALGORITHM = ...] [LOCK = ...];

ALTER TABLE table_name DROP INDEX index_name [, DROP INDEX other_index ...];
ALTER TABLE table_name DROP PRIMARY KEY;

SHOW INDEX FROM customers;

Indexes on customers

2. Dropping an Index

DROP INDEX idx_spend ON customers;

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

Dropping a secondary index is fast: InnoDB just marks its pages as free. Several at once with ALTER TABLE:

CREATE INDEX tmp1 ON customers (first_name);
CREATE INDEX tmp2 ON customers (signup_on);
ALTER TABLE customers DROP INDEX tmp1, DROP INDEX tmp2;

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

3. Dropping a UNIQUE Index

A UNIQUE constraint is an index, so it is removed the same way. After that, duplicates are allowed again:

DROP INDEX uq_email ON customers;
INSERT INTO customers (email, first_name, last_name, city, signup_on, spend)
VALUES ('user1@mail.example', 'Test', 'Dup', 'Goa', '2026-03-01', 0);
SELECT COUNT(*) AS rows_with_this_email FROM customers WHERE email = 'user1@mail.example';

Result: 1 row

4. Dropping a Primary Key

Use DROP PRIMARY KEY. If the key column is AUTO_INCREMENT, MySQL refuses because an AUTO_INCREMENT column must be indexed:

ALTER TABLE customers DROP PRIMARY KEY;

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

5. Indexes Needed by Foreign Keys

A foreign key needs an index on its column. MySQL won't drop the only index that supports it:

DROP INDEX idx_customer ON orders;

ERROR 1553 (HY000): Cannot drop index 'idx_customer': needed in a foreign key constraint

Drop the foreign key first (and re-create it later if needed), or create another index that starts with the same column:

CREATE INDEX idx_customer_total ON orders (customer_id, total);
DROP INDEX idx_customer ON orders;
SHOW INDEX FROM orders;

Result: 3 rows

6. Finding Indexes to Drop

Redundant indexes: an index that is a left prefix of another. Here idx_city is covered by idx_city_last:

SELECT a.index_name AS redundant, b.index_name AS covered_by
FROM (SELECT index_name, GROUP_CONCAT(column_name ORDER BY seq_in_index) AS cols
      FROM information_schema.statistics WHERE table_schema = 'crm' AND table_name = 'customers' GROUP BY index_name) a
JOIN (SELECT index_name, GROUP_CONCAT(column_name ORDER BY seq_in_index) AS cols
      FROM information_schema.statistics WHERE table_schema = 'crm' AND table_name = 'customers' GROUP BY index_name) b
  ON a.index_name <> b.index_name AND b.cols LIKE CONCAT(a.cols, ',%');

Result: 1 row

On a running server, the sys schema helps too: sys.schema_redundant_indexes and sys.schema_unused_indexes (unused since the last restart).

7. Test with an Invisible Index First

ALTER TABLE customers ALTER INDEX idx_city INVISIBLE;
EXPLAIN SELECT id FROM customers WHERE city = 'Goa';

Result: 1 row

Queries on city still use idx_city_last, so nothing gets slower. It's safe to drop:

DROP INDEX idx_city ON customers;

Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

8. Checklist

1. Confirm the index is unused or redundant (sys schema, EXPLAIN of important queries).
2. Check it isn't the only index for a foreign key and isn't a UNIQUE rule the business needs.
3. Make it INVISIBLE and watch for slow queries for a while.
4. Drop it; keep its definition (SHOW CREATE TABLE) in case you need to recreate it.

9. Summary

• DROP INDEX name ON table; or ALTER TABLE … DROP INDEX name removes an index.
• UNIQUE constraints are dropped as indexes; primary keys with DROP PRIMARY KEY.
• Indexes required by foreign keys can't be dropped until another index or the FK change covers it.
• Find redundant and unused indexes, test with INVISIBLE, then drop.