Databases Reference
In-Depth Information
4
Selecting Indexes
Give no decision till both sides thou'st heard.
—Phocylides (6th century
B
.
C
.)
he B+tree, as we described in detail in Chapter 2, has evolved to become the most
popular indexing method for relational databases, replacing many other methods
used in the past. However, the task of index selection for a database is still a complex one,
walking the fine line between art and science. Decisions still must be made re-garding
which keys and non-key attributes to index, equality vs. range queries, composite indexes
with many keys, and index main-tenance. This chapter helps clarify these decisions.
T
4.1
Indexing Concepts and Terminology
Indexes are one of the primary tools used in database management systems (DBMSs) to
deliver high performance (i.e. low response time) for the wide range of queries typically
requested of these sys-tems. Indexes have a wide range of purposes in terms of perform-
ance: fast lookup for specific data and ranges of data, uniqueness enforcement, and so
on.. After a database has gone through the logical design of the schema to make sure it is
clean and coherent, it is implemented using a commercial system. Soon after that point,
when the system has been running and monitored, performance tuning is often done to
increase the database system throughput and reduce the database response time, includ-
ing service time and queuing delays, for a set of transactions—queries and updates. The
53
