Using Indexes Effectively
Indexes are the single most important part of a relational database for improving query performance. The subject of indexes in databases is very broad. This section starts with a discussion of different types of indexes. It then moves on to the types of queries that can benefit from indexes and how to look at a query from the perspective of “what is the best index.”
What Are Indexes?
An index is a supporting data structure that speeds access to specific rows in a table. You do not query indexes directly. Instead, the query optimizer knows about them and decides when to use one in addition to—or even instead of—the original table.
Earlier, this chapter discussed a simple way to think of an index: as a table with columns sorted along with a row identifier. By providing additional information about where values are located, indexes can radically speed up queries.
The syntax for creating an index is:
CREATE INDEX <index name> ON...