How to Drop an Index in MySQL: Syntax and Examples

To drop an index in MySQL, use the DROP INDEX index_name ON table_name; statement. This removes the specified index from the table without affecting the table data.

Syntax

The syntax to drop an index in MySQL is:

• DROP INDEX index_name ON table_name;

Here, index_name is the name of the index you want to remove, and table_name is the table where the index exists.

This command deletes the index but keeps the table and its data intact.

sql

DROP INDEX index_name ON table_name;

Example

This example shows how to drop an index named idx_customer_name from the customers table.

sql

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(50),
  INDEX idx_customer_name (name)
);

-- Drop the index
DROP INDEX idx_customer_name ON customers;

Output

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Common Pitfalls

Common mistakes when dropping indexes include:

• Using the wrong index name, which causes an error.
• Trying to drop the primary key index with DROP INDEX (use ALTER TABLE DROP PRIMARY KEY instead).
• Not specifying the table name after DROP INDEX, which is required in MySQL.

Always verify the index name with SHOW INDEX FROM table_name; before dropping.

sql

/* Wrong: Missing table name */
DROP INDEX idx_customer_name;

/* Correct: Include table name */
DROP INDEX idx_customer_name ON customers;

Output

ERROR 1064 (42000): You have an error in your SQL syntax;
Query OK, 0 rows affected (0.01 sec)

Quick Reference

Command	Description
DROP INDEX index_name ON table_name;	Removes the specified index from the table.
SHOW INDEX FROM table_name;	Lists all indexes on the table.
ALTER TABLE table_name DROP PRIMARY KEY;	Drops the primary key index.

Key Takeaways

Use DROP INDEX index_name ON table_name; to remove an index in MySQL.

Always specify the table name when dropping an index.

Do not use DROP INDEX to remove primary keys; use ALTER TABLE instead.

Check existing indexes with SHOW INDEX FROM table_name; before dropping.

Dropping an index does not delete table data.