How to Drop an Index in MySQL using Drop Index and Alter Table Commands

To drop a non-primary key index, use the DROP INDEX command:

DROP INDEX index_name ON table_name;

The syntax requires the table name to be specified because MySQL allows index names to be reused on multiple tables.

Primary keys in MySQL are always named PRIMARY (not case sensitive). But because PRIMARY is a reserved keyword, backticks are required when you use it in the DROP INDEX command.

--Enclose PRIMARY in backticks to refer to the name, not the reserved word
DROP INDEX `PRIMARY` ON table_name;

Alternatively, MySQL also allows to drop indexes using the ALTER TABLE command:

-- Drop a non-primary key index
ALTER TABLE table_name DROP INDEX index_name;

-- Drop the primary key. This time we are using the reserved words so no backticks
ALTER TABLE table_name DROP PRIMARY KEY;