Datasheet

Book VII
Chapter 1
Tuning the
Database
585
Figure 1-1 reveals a few things about the cost of indexes:
✦ A clustered index always performs better than an unclustered index.
✦ A clustered index performs better than a sequential scan unless practi-
cally all the rows are retrieved.
✦ When one record is being retrieved, or a very few records are being
retrieved, a clustered index performs much better than a sequential
scan.
✦ When one record is being retrieved, or a very few records are being
retrieved, an unclustered index performs better than a sequential scan.
✦ When more than about 10 percent of the records in a table are retrieved,
a sequential scan performs better than an unclustered index.
That last point disproves the myth that indexing a table column that is used
as a retrieval key always improves performance compared with the perfor-
mance of a sequential scan.
Choosing an index type
In most cases, a B+ tree index is preferred because it does a good job on
range queries as well as equality queries. Hash indexes are slightly better
than B+ tree indexes in equality queries but not nearly as good in range que-
ries, so overall, B+ tree indexes are preferred.
In some cases where a retrieval is made of data contained in multiple tables,
however, a hash index will do better. One such case involves a nested loop
join, in which the inner table is the indexed table and the index includes the
join columns. (This situation is called a hash join.) Because an equality selec-
tion is generated for each row in the outer table, the advantage of the hash
index over the B+ tree index is multiplied. Another case in which the hash
join comes out ahead is when there is an important equality query and there
are no range queries on a table.
You don’t need to lose a lot of sleep over choosing an index type. Most data-
base engines make the choice for you, and that choice usually is the best one.
Weighing the cost of index maintenance
Indexes slow update operations because every time a table is updated with
an insertion or a deletion, all its indexes must be updated as well. Balance
this situation against the speed gained by accessing table rows faster than
would be possible with a sequential table scan. Even updates are poten-
tially speeded because a row must be located before it can be updated.
Nevertheless, you may find that the net benefit of some indexes doesn’t
justify their inclusion in the database, and you’re better off dropping them.
Choosing the Right Indexes
40_9780470929964-bk07ch01.indd 58540_9780470929964-bk07ch01.indd 585 2/24/11 3:45 PM2/24/11 3:45 PM