SQL > ALTER TABLE > Drop Index Syntax

Sometimes we may decide that a particular index is no longer needed for a table. In those cases, that index should be removed to free up storage. To drop an index in SQL, we specify that we want to change the table structure via the ALTER TABLE command, followed by the DROP INDEX command.

ALTER TABLE DROP INDEX removes an index from a table, freeing storage and reducing write overhead. This syntax is supported in MySQL but not Oracle or SQL Server, which use a standalone DROP INDEX statement.

The SQL syntax to drop an index from a table is, 

ALTER TABLE "table_name"
DROP INDEX "index_name";

Let's look at the example. Assuming our starting point is the Customer table created in the CREATE TABLE section:

Table Customer

 Column Name  Data Type 
 First_Name  char(50) 
 Last_Name  char(50) 
 Address  char(50) 
 City  char(50) 
 Country  char(25) 
 Birth_Date  datetime 

Assume we want to drop the index created in the ALTER TABLE ADD INDEX section. To do this, we type in the following: 

ALTER TABLE Customer DROP INDEX IDX_COUNTRY;

Please note that using ALTER TABLE to drop an index in supported in MySQL but not in Oracle or SQL Server.

Frequently Asked Questions

Q: Does ALTER TABLE DROP INDEX work in Oracle and SQL Server?
A: No. ALTER TABLE DROP INDEX is MySQL-specific. In Oracle and SQL Server, you drop an index using the standalone DROP INDEX statement: DROP INDEX index_name;
Q: When should I drop an index?
A: Drop an index when it is no longer used in queries, when it causes excessive overhead on INSERT/UPDATE/DELETE operations, or when you are rebuilding it with a better definition.
Q: Does dropping an index delete any data?
A: No. Dropping an index only removes the index structure used to speed up queries. The underlying table data is not affected.
Q: How do I see which indexes exist on a table in MySQL?
A: Use SHOW INDEX FROM table_name; to list all indexes on a table in MySQL.

Related Topics

Next: SQL ADD CONSTRAINT

This page was last updated on March 19, 2026.



Copyright © 2026   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact