8.7. Dropping Indexes

To drop an index from a table, use ALTER TABLE or DROP INDEX.

With ALTER TABLE, use a DROP clause and name the index to be dropped. Dropping a PRIMARY KEY is easy:

 ALTER TABLE HeadOfState DROP PRIMARY KEY; 

To drop another kind of index, you must specify its name. If you don't know the name, you can use SHOW CREATE TABLE to see the table's structure, including any index definitions, as shown here:

 mysql> SHOW CREATE TABLE HeadOfState\G *************************** 1. row ***************************        Table: HeadOfState Create Table: CREATE TABLE `HeadOfState` (   `ID` int(11) NOT NULL default '0',   `LastName` char(30) NOT NULL default '',   `FirstName` char(30) NOT NULL default '',   `CountryCode` char(3) NOT NULL default '',   `Inauguration` date NOT NULL default '0000-00-00',   KEY `NameIndex` (`LastName`,`FirstName`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 

The KEY clause of the output shows that the index name is NameIndex, so you can drop the index using the following statement:

 ALTER TABLE HeadOfState DROP INDEX NameIndex; 

After you've dropped an index, you can recover it merely by re-creating it:

 ALTER TABLE HeadOfState ADD INDEX NameIndex (LastName, FirstName); 

Dropping an index differs from dropping a database or a table, which cannot be undone except by recourse to backups. The distinction is that when you drop a database or a table, you're removing data. When you drop an index, you aren't removing table data, you're removing only a structure that's derived from the data. The act of removing an index is a reversible operation as long as the columns from which the index was constructed have not been removed. However, for a large table, dropping and recreating an index may be a time-consuming operation.

To drop an index with DROP INDEX, indicate the index name and table name:

 DROP INDEX NameIndex ON t; 

To drop a PRIMARY KEY with DROP INDEX, refer to the index name (PRIMARY), but use a quoted identifier because this name is a reserved word:

 DROP INDEX `PRIMARY` ON t; 

Unlike ALTER TABLE, the DROP INDEX statement can drop only on a single index per statement.