Understanding Indexes

An index is a fast way to find data. At a simple level, an index works like the tabs on a large dictionary; you can go directly to all the words that begin with a particular letter. After that, you need to do some additional searching, taking advantage of the fact that the words are stored in alphabetical sequence. The range of words on a page is generally printed at the top, so you do not have to scan through individual words until you find the page you want. Many database systems include a type of index, often called a primary or clustered index, that has the same sequence as the data.

But suppose you are searching an atlas, where data is generally stored in geographical sequence. If you are looking up Majorca, you are likely to look it up first in the alphabetical index, and then search the page that has a map of the Mediterranean. Data is accessed through an attribute different from its storage attribute.

Suppose you wan to find all the words in the dictionary derived from Finnish words. Unless you had a dictionary with an etymological index, your scan of the data pages would be very time-consuming. This type of access should be avoided when accessing large database tables, because it is slow even on the fastest server.

Typically, the Where clause in a query contains a mixture of criteria resolvable through an index and criteria resolvable only through access to the data pages. To be efficient, use the index criteria to limit the number of data rows searched.

Multi-Column Indexes

Relational databases allow indexes over multiple columns, so that if you have Where criteria for two or more columns in the index, the database manager can use one index to satisfy criteria on multiple columns at a time. Having the pertinent criteria columns in an index, however, does not guarantee that index will be used or that it will be used effectively on all the columns that have criteria.