Selecting Indexes - Physical Database Design - page 59

first declare the PKs and index the FKs, regardless of the query mix you are given.
For applications that are very demanding in terms of frequency, heavy volume of
data accessed, and/or priority, adding or modifying indexes may need to be consid-
ered if bottlenecks appear.
If you decide you need an index, look at the WHERE (and AND) clause of a query
to determine which attributes can be used for index search keys. Each query may
involve different attributes, and indexes should be chosen for each query that can
significantly improve the database performance. Queries that require full table
scans may not need any indexing. Indexes that speed up more than one query will
be the most valuable.
Design Decision 2: When do I need multi-attribute (composite) search keys, and
which ones should I choose? A multipoint query involves a WHERE clause with
multiple attributes. When this type of query occurs, a composite B+tree index
should be used to efficiently access the set of rows that satisfies all the search criteria
in the query. It was shown in Chapter 2 that a single composite index on n
attributes can be significantly faster than n separate indexes on the same attributes
because the separate index approach must include a merge step to find the intersec-
tion RIDs for the query. Tradeoff analysis between full table scans and multi-
attribute B+tree indexes is shown in Section 2.5 (see Figure 4.1 for a summary).

Figure 4.1 Comparison of composite index and scan I/O time for the same query.