Database Reference
In-Depth Information
For many years, inadequate indexing has been the most common cause of
performance disappointments. The most widespread problem appears to be that
indexes do not have sufficient columns to support all the predicates of a WHERE
clause. Frequently, there are not enough indexes on a table; some SELECTs may
have no useful index; sometimes an index has the right columns but in the
wrong order.
It is relatively easy to improve the indexing of a relational database because
no program changes are required. However, a change to a production system
always carries some risk. Furthermore, while a new index is being created, update
programs may experience long waits because they are not able to update a table
being scanned for a CREATE INDEX. For these reasons, and, of course, to
achieve acceptable performance from the first production day of a new applica-
tion, indexing should be in fairly good shape before production starts. Indexing
should then be finalized soon after cutover, without the need for numerous exper-
iments.
Database indexes have been around for decades, so why is the average quality
of indexing still so poor? One reason is perhaps because many people assume
that, with the huge processing and storage capacity now available, it is no longer
necessary to worry about the performance of seemingly simple SQL. Another
reason may be that few people even think about the issue at all. Even then, for
those who do, the fault can often be laid at the door of numerous relational
database textbooks and educational courses. Browsing through the library of
relational database management system (DBMS) topics will quite possibly lead
to the following assessment:
ž The index design topics are short, perhaps only a few pages.
ž The negative side effects of indexes are emphasized; indexes consume disk
space and they make inserts, updates, and deletes slower.
ž Index design guidelines are vague and sometimes questionable. Some writ-
ers recommend indexing all restrictive columns. Others claim that index
design is an art that can only be mastered through trial and error.
ž Little or no attempt is made to provide a simple but effective approach to
the whole process of index design.
Many of these warnings about the cost of indexes are a legacy from the 1980s
when storage, both disk and semiconductor, was significantly more expensive
than it is today.
MYTHS AND MISCONCEPTIONS
Even recent topics, such as one published as late as 2002 (1), suggest that only
the root page of a B-tree index will normally stay in memory. This was an
appropriate assumption 20 years ago, when memory was typically so small that
the database buffer pool could contain only a few hundred pages, perhaps less
than a megabyte. Today, the size of the database buffer pools may be hundreds
Search WWH ::




Custom Search