Creating and Managing Indexes

Indexes are often the most significant factor affecting how rapidly data can (or cannot) be retrieved from large tables.

This section provides the basic details on how to manage indexesit does not cover the topic of how to choose which columns to index or how indexes are used. For more information, read Chapter 4, "Retrieving Data: Simple Queries."

Adding an Index to a Table

CREATE INDEX index_name        ON table_name (column_name, ...); CREATE INDEX author ON book (author); CREATE INDEX author ON book (author(16)); CREATE INDEX title_author ON book (title, author);

To add an index to a set of columns in an existing table, you use the CREATE INDEX command (or the ALTER TABLE command, but this book covers CREATE INDEX, as it is easier to remember).

The command requires that you specify a name for the new index along with the name of the table in which to create the index and the column (or columns) on which to create the index.

The first query shows a generic form of the command, where you would need to include your own specific values. The remaining queries show specific examples that add various indexes to the book table.

Renaming an Index

Renaming indexes is done infrequently. An index is usually simply deleted or its definition changed. If you do want to rename an index, delete the index and re-create it.

Deleting an Index

DROP INDEX index_name ON table_name; DROP INDEX author ON book;

The syntax for deleting an index is very simpleyou merely state which index to remove from which table.

The first query shows the generic form of the query. The second query deletes an index called author from a table called book.