Database Reference
In-Depth Information
Chapter 4
Deriving the Ideal Index
for a SELECT
ž Guidelines for the major performance factors concerning table and index
scans
ž Random and sequential read times and CPU costs
ž Assigning stars to an index for a SELECT statement, according to the
three most important requirements
ž The design of a three-star index—the ideal index for the statement
ž Fat indexes
ž Algorithm to design the best index for a SELECT statement
ž Consideration of the existing indexes to determine the most practical
index, taking into account CPU time, disk read time, and elapsed time
ž Implications of any suggested changes to the current indexes with regard
to the maintenance overheads
ž Response time, drive load, and disk costs
ž Recommendations
INTRODUCTION
Many DBAs appear to be satisfied if all the SQL calls in a program use one or
more index. Everything looks normal; “the EXPLAIN is clean.” An inappropriate
index, however, may lead to worse performance than a scan of the whole table.
Mark Gurry (2) agrees:
I am astounded at how many tuners, albeit inexperienced, believe that if an
SQL statement uses an index, it must be well tuned. You should always ask, “Is
it the best available index?” or “Could an additional index be added to
improve the responsiveness?” or “Would a full table scan produce the result
faster?” (page 47)
In this chapter we are going to consider these extremely important questions
in considerable detail, but first we will need to define the assumptions on which
our analysis will be based.
Search WWH ::




Custom Search