What's changed: Initial version
5.1Index design
Covers when to use a B-tree index for fast lookups, a hash index for equality search, and a bitmap index for low-selectivity columns; the relationship between column order in a composite index and selectivity; and the trade-off an index imposes on update performance.
For a database designer, it is not simply a matter of "more indexes are always better." While an index speeds up lookups, it must also be maintained on every row insert, update, and delete, so you must judge which columns to index, with which index type, and in what column order, based on estimating both read and write load. This section covers the characteristics of index structures and the design judgment needed to improve a slow query.
5.1.1How a B-tree index works and when it fits
- A B-tree index organizes key values into a tree structure that preserves ordering, letting a search traverse from root to leaf to locate the target row in roughly
O(log n). It is the most general-purpose index structure and the default index type in most RDBMSs. - Because the tree structure preserves ordering, it handles not only equality search (
=) but also range search (>,<,BETWEEN) and sorting (ORDER BY) well. Most business queries—extracting a date range, prefix-matching a sequential number, etc.—can be sped up sufficiently with a B-tree.
Continue reading — free sign-up
You're reading the free preview. Sign up free to read this section in full, plus every chapter (including 4+) and all questions.