MySQL DROP INDEX Statement (original) (raw)

Last Updated : 23 Jul, 2025

MySQL, an open-source relational database management system, is widely used for web-based applications. Managing indexes in MySQL is important for maintaining optimal database performance. Indexes improve query speed by allowing quick data retrieval, but sometimes you may need to remove an index to optimize performance or reclaim disk space. In this article, we'll explore how to use the DROP INDEX statement in **MySQL.

What is the DROP INDEX Statement?

The **DROP INDEX statement in MySQL is used to delete an existing index from a table. Removing unnecessary or redundant indexes can help optimize database performance and manage storage effectively.

**Syntax of DROP INDEX:

The basic syntax for the DROP INDEX statement is as follows:

DROP INDEX index_name ON table_name;

index_name : The name of the index you want to drop.
table_name : The name of the table from which the index will be dropped.

Example of DROP INDEX

Create a Table and Insert Data

First, we'll create a **products**table and insert some sample data.

CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(100),
category VARCHAR(50),
price DECIMAL(10, 2)
);

INSERT INTO products (product_name, category, price) VALUES
('Laptop', 'Electronics', 1200.00),
('Smartphone', 'Electronics', 800.00),
('Coffee Maker', 'Appliances', 100.00),
('Running Shoes', 'Clothing', 80.00),
('Digital Camera', 'Electronics', 500.00);

Create an Index

Next, we create an index on the category column.

CREATE INDEX idx_category ON products (category);

**Explanation: This command creates an index named **idx_category**on the category column of the products table. This index helps speed up queries that filter by the category column.

Verify the Index

To verify the index creation, use the**SHOW INDEX** statement.

SHOW INDEX FROM products;

**Expected Output:

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
products	1	idx_category	1	category	A	1	NULL	NULL	YES	BTREE	YES	NULL
products	0	PRIMARY	1	product_id	A	5	NULL	NULL	BTREE	YES	NULL

**Explanation: This output shows two indexes: the PRIMARY index on product_id and the **idx_category**index on the category column.

Drop the Index

Now, let's drop the **idx_category**index.

DROP INDEX idx_category ON products;

**Explanation: This command removes the **idx_category**index from the products table.

Verify the Index Deletion

To confirm the index has been dropped, use the SHOW INDEX statement again.

SHOW INDEX FROM products;

**Expected Output:

Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment	Visible	Expression
products	0	PRIMARY	1	product_id	A	5	NULL	NULL	BTREE	YES	NULL

**Explanation: The output now only shows the PRIMARY index on product_id, indicating that the idx_category index has been successfully dropped.

Conclusion

Dropping an index in MySQL is a simple but powerful operation that can impact database performance. In this example, we demonstrated how to create a table, add an index, verify its existence, drop the index, and then confirm its removal. Properly managing indexes is necessary for optimizing query performance and efficient database management.