Databases Reference
In-Depth Information
that most DBMSs sort the data using more efficient algorithms, and then build the
actual index.
Rule 10. Avoid extremes in index cardinality and value distribution.
Database
users with experience in performance tuning often find that they need to avoid
index distributions that are extremely skewed. Don't allow just a few index values to
have much higher levels of frequency than other values. When this occurs, you
need to redesign the index to get a more balanced distribution and avoid costly
CPU and I/O delays for certain types of updates.
Also, you want to avoid extremes in index cardinality. For example, you want to
avoid indexes with only one entry, particularly for tables with over 1,000 rows. You
also want to avoid indexes so large that they begin to approximate (above 75%) the
number of rows in the table you are indexing.
Rule 11. Covering indexes (index only) are useful, but often overused.
Index-only
usage can often be very useful to search indexes and avoid I/Os within the database.
However, as with most indexing features, they can also be overused, resulting in
extremely query-specific indexes with very long keys and fewer keys per index page,
thus more I/O.
Rule 12. Use bitmap indexes for high-volume data, especially in data ware-
houses.
Large data stores, such as in data warehouses, that involve terabytes or
petabytes, tend to have extremely large (B+tree) indexes. Indexes can be effectively
compressed with bitmap structures, which save space and time for many queries.
4.3
Index Selection Decisions
Now that we have enumerated the basic rules for indexing, we shall consider the design
decisions the practitioner will face when tuning a database. In general, we want to con-
sider additional indexes incrementally and see whether we could have a better plan, for
example, whether the performance (throughput and response time) significantly
improves for each new index. There is no standard rule here, but they must be set up
individually for each database system and user.
We also need to consider updates. Sometimes a new index improves query perfor-
mance but severely degrades update performance. It is the combined performance for
query and update that will determine whether a new index is useful. This is a common
tradeoff in index design, and requires that we document what updates are needed, what
their frequencies are, and which tables they apply to.
Many of the following critical design questions were defined in the excellent data-
base book by Ramakrishnan [2004]. They have been modified and extended here.
Design Decision 1: Does this table require an index or not, and if so which search
key should I build an index on?
A reasonable starting point for index design is to
