Removing indexes from tables

Once-useful indexes may become obsolete as requirements change with the evolving database. In this chapter, we will show you how to get rid of the IDX_author index created in the Adding indexes to tables recipe.

Getting ready

Connect to the database server with your administrative account.

How to do it...

1. Change the default database to library:

   USE library;
   

2. Drop the IDX_author index using the following command:

   ALTER TABLE books DROP INDEX IDX_author;
   

How it works...

Using the ALTER TABLE statement, we tell the database that we want to remove (DROP) the index named IDX_author from the books table.

There's more...

As with the creation of new indexes, you can drop multiple indexes at once using the ALTER TABLE statement by simply adding more DROP INDEX clauses, separated by comma. If you were to delete both indexes defined in Adding indexes to tables, you could use this statement:

ALTER TABLE books DROP INDEX IDX_author, DROP INDEX IDX_title;

See also

• Adding indexes...