SQL Performance Tuning

"Indexes make SELECTs faster, but they make UPDATEs slower."

Common knowledge

The common knowledge about indexes is truebut it's too vague for our purposes. In this chapter, we'll try to be more precise about indexes. Specifically, we'll look at these questions:

• What precisely is an index?

• How much does an index accelerate a SELECT?

• What do you, as an application programmer, need to know about the costs, the alternatives, the work arounds, the rules, and the concepts of dealing with indexes?

The pleasant thing to note about the technology of indexing is that it's mature. Nearly all DBMSs (including the Big Eight) depend on the B-tree structure for their indexes. B-trees have been around for over 30 years , so their use is very well known. Many DBMSs also use the bitmap (bitmap index), an interesting technology but by no means a new idea either. A few also use hashes for indexing, though hashes are typically hidden from the end user . Thus, despite vendors ' attempts to differentiate their products, index implementations are the same. They have to be, because it's been established through years of practice what the better techniques are. That's good news for you! When you switch DBMSs, you won't have to relearn everything, and when you grow old, the kids will still understand you when you tell your index war stories.