Proactive Index Design - Relational Database Index Design and the Optimizers - page 79

Comment

As the QUBE is a very rough formula, it is misleading to show more than one
significant digit in the result, although we do it in this topic for the sake of clarity.
In a real report, the conclusion should be formulated such as: According to a quick
estimate, the proposed index reduces the worst-case response time from 2 min to 1 s.

Cheapest Adequate Index or Best Possible Index

Indexing is not simply about minimizing the total number of disk I/Os; it is to
try to make all programs fast enough without using an excessive amount of disk
storage, memory and read cache, and without overloading the disk. All these
issues were discussed in some detail in Chapter 4.
It is even possible to express the decision in numerical terms, such as to
ask: “Is it worth paying U.S.$10 per month to decrease the response time of a
transaction that is run 50,000 times a month, from 2 to 10 s to 0.1 to 0.5 s, and
to save 1000 CPU seconds per month?”
Having determined, by applying the BQ and/or the QUBE (or some other
method), that CURSOR55 is too slow because of inadequate indexing, we face
a difficult question: Should we go for the best possible index for the cursor or
choose the cheapest index improvement that results in adequate performance? Or
perhaps something between the two? We don't have a rigid formula with which
to answer this question, but the ideas we discussed in Chapter 4 should enable
us to design a suitable index.

Best Index for the Transaction

By applying the algorithm described in Chapter 4, we can determine that there
are two possible three-star indexes for candidate A:

(LNAME, CITY, FNAME, CNO) or

(CITY, LNAME, FNAME, CNO).

These both have three stars because they would scan a very thin slice of the
index (2 MC); the ORDER BY column follows the matching columns (both used
with equal predicates) in the index, thereby negating the need for a sort; and the
query would be index only.
As no sort is required, there is no need to consider candidate B.

TS = 1% × 10% × 1,000,000 Index LNAME, CITY, FNAME, CNO TR = 1

or CITY, LNAME, FNAME, CNO
Fetch 1000 × 0.1 ms

LRT TR = 1 TS = 1000

1 × 10 ms 1000 × 0.01 ms
10 ms + 10 ms + 100 ms = 120 ms