How, What, and Why to Index

Indexes speed the retrieval of data. An index on a column can often make the difference between a nearly immediate response to a query and a long wait.

So why not index every column? The most significant reason is that building and maintaining an index takes time and storage space on the database device.

A second reason is that inserting, deleting, or updating data in indexed columns takes a little longer than in unindexed columns because of the time it takes the system to maintain the index when key values are changed. However, this cost is usually outweighed by the extent to which indexes improve retrieval performance.

In general, it's usually appropriate to put indexes on columns you use frequently in retrievals, especially primary key columns and columns used in joins and sorts. Here are some more precise guidelines:

• A unique index on the column or columns that store the table's primary key prevents duplicates and guarantees that every value in the primary key column will in fact uniquely identify the row. The primary key is frequently joined to columns in other tables, so the index is useful for that, in addition to guaranteeing uniqueness.

• A column that is often accessed in sorted order probably should be indexed so that the system can take advantage of the indexed order.

• Columns that are regularly used in joins should be indexed because the system can perform the join faster.

• A column that is often searched for ranges of values might be a good choice for indexing if your system provides clustered indexes. Once the row with the first value in the range is found, rows with subsequent values are guaranteed to be physically adjacent. A clustered index does not offer as much of an advantage for searches on single values.

These are some cases in which indexes are not useful:

• Columns that are rarely referenced in queries don't benefit from indexes in terms of performance because the system seldom or never has to search for rows on the basis of values in these columns. However, you may still want to use an index to enforce uniqueness.

• Columns that can have only two or three values (for example, male, female, unknown) gain no real advantage from being indexed.

• Small tables with few rows don't get a performance boost from indexes: The system will generally choose a table scan (examining the table rows one by one) instead of using an index. The length of time it takes to perform a scan is related to the number of rows in the table.

Indexes are a very complex issue. To use them well, you need to understand how your system optimizes queries and what performance issues your application has—topics not covered in this book. Generally speaking, you should start by picking what seem to be reasonable indexes and then look for performance problems during your prototyping, benchmarking, and testing phases. When you have a clear idea of your needs, you can adjust the indexes to meet them.