Composite Index

When you include more than one column in an index, it’s referred to as a composite index. As the number of columns grows, or the number of bytes in the key grows, the effectiveness of the index is reduced. The problem is that the index is sorted by the first column values. So the second column in the index is more or less only useful if you need the first column as well. Even so, a composite index is often good to have when users are querying with predicates on all of the columns involved. The order of the columns in a query is important with respect to whether a composite can and will be used. There are a couple important considerations:

·         Which column is most selective? If one column includes unique or mostly unique values, this is possibly a good candidate for the first column. The key is that the first column is the one by which the index is sorted. Searching on the second column only is less valuable (though queries using only the second column can scan the index leaf pages for values).

·         Which column is used most often without the other columns? One composite index can be useful to several different queries, even if only the first column of the index is all that is being used in those queries.

Composite indexes have these advantages:

• A composite index provides opportunities for index covering.

• If queries provide search arguments on each of the keys, the composite index requires fewer I/Os than the same query using an index on any single attribute.

• A composite index is a good way to enforce the uniqueness of multiple attributes.

Good choices for composite indexes are:

• Lookup tables

• Columns that are frequently accessed together

• Columns used for vector aggregates

• Columns that make a frequently used subset from a table with very wide rows

The disadvantages of composite indexes are:

• Composite indexes tend to have large entries. This means fewer index entries per index page and more index pages to read.

• An update to any attribute of a composite index causes the index to be modified. The columns you choose should not be those that are updated often.

Poor choices are:

• Indexes that are nearly as wide as the table

• Composite indexes where only a minor key is used in the where clause