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.
Using the ALTER TABLE statement, we tell the database that we want to remove (DROP) the index named IDX_author from the books table.
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;