Deriving the Ideal Index for a SELECT - Relational Database Index Design and the Optimizers - page 58

Number of 4K leaf pages ( 14 columns ) 1 . 5 × 10 , 000 × 200 / 4 , 000 = 800

Sequential read time ( 4 columns ) = 200 × 0 . 1ms = 20 ms

80 ms
As sequential processing remains CPU bound , there is no noticeable difference
in the elapsed time. The current 4-column index becomes superfluous when the
new 14-column index is created. Chapter 15 discusses CPU estimation in con-
siderable detail.

Sequential read time ( 14 columns ) = 800 × 0 . 1ms =

Possibly Superfluous Indexes

A common situation: The ideal index for a new SELECT is (A, B, C, D, E, F).
The table already has index (A, B, F, C). Is the new index superfluous if the
existing index is replaced by index (A, B, F, C, D, E)? In other words is the
existing index adequate for the new SELECT, if columns D and E are added to
it to make the access path index only?
The proposed ideal index may be better than index (A, B, F, C, D, E) in
two respects:
1. There may be more matching columns.
2. It may prevent a sort.
Both advantages are influenced by the number of rows that must be scanned
in the index slice. This difference can be converted to milliseconds, as we have
done in this chapter, or more easily by the Quick Upper-Bound Estimate (QUBE)
discussed in Chapter 5. More often that not, the estimates will indicate that a new
index is not needed; an existing index with the new columns added at the end,
will be adequate for the new SELECT.

COST OF AN ADDITIONAL INDEX

If 100 different SELECT statements access a table and the best possible index is
designed for each SELECT, we may end up with dozens of indexes on the table
even if no identical twins are created. INSERTs, UPDATEs, and DELETEs to
the table may then become too slow.

Response Time

When the DBMS adds a table row, it must add a corresponding row to each
index. With current hardware, adding a row to an index typically adds 10 ms to
the elapsed time of the INSERT call because a leaf page must be read from
a disk drive. When a transaction inserts a row to a table with 10 indexes,
index maintenance may add 10 100 ms to the response time, which

is probably acceptable. However, if one transaction adds 20 rows to a table
with 10 indexes, index maintenance may require 181 random reads, 1.8 s. This

× 10 ms =