Introduction - Relational Database Index Design and the Optimizers - page 9

Detect SELECT statements that are
too slow due to inadequate indexing

1

Worst input: Variable values leading to
the longest elapsed time

Design indexes that make all SELECT
statements fast enough

2

Table maintenance (INSERT, UPDATE,
DELETE) must be fast enough as well

Figure 1.2 Systematic index design.

The first attempts to detect inadequate indexing at design time were based
on hopelessly complex prediction formulas, sometimes simplified versions of
those used by cost-based optimizers. Replacing calculators with programs and
graphical user interfaces did not greatly reduce the effort. Later, extremely simple
formulas, like the QUBE, developed in IBM Finland in the late 1980s, or a simple
estimation of the number of random I/Os were found useful in real projects. The
Basic Question proposed by Ari Hovi was the next and probably the ultimate
step in this process. These two ideas are discussed in Chapter 5 and widely used
throughout this topic.
Methods for improving indexes after production cutover developed signifi-
cantly in the 1990s. Advanced monitoring software forms a necessary base to do
this, but an intelligent way to utilize the massive amounts of measurement data
is also essential.
This second task of systematic index design went unrecognized for a long
time. The SELECTs found in textbooks and course material were so unreal-
istically simple that the best index was usually obvious. Experience with real
applications has taught, however, that even harmless looking SELECTs, par-
ticularly joins, often have a huge number of reasonable indexing alternatives.
Estimating each alternative requires far too much effort, and measurements even
more so. On the other hand, even experienced database designers have made
numerous mistakes when relying on intuition to design indexes.
This is why there is a need for an algorithm to design the best possible index
for a given SELECT. The concepts of a three-star index and the related index
candidates, which are considered in Chapter 4, have proved helpful.
There are numerous success stories regarding the application of these simple,
manual index design algorithms. It is not uncommon to see the elapsed times of
SELECT calls being reduced by two orders of magnitude; from well over a minute
down to well under a second, for instance, with relatively little effort, perhaps
from as little as 5 or 10 min with the methods recommended in Chapters 4, 5, 7,
and 8.