When to Use a Multi-column Index

Go Up to Creating Indexes

The main reason to use a multi-column index is to speed up queries that often access the same set of columns. You do not have to create the query with the exact column list that is defined in the index. InterBase will use a subset of the components of a multi-column index to optimize a query if the:

• Subset of columns used in the ORDER BY clause begins with the first column in the multi-column index. Unless the query uses all prior columns in the list, InterBase cannot use that index to optimize the search. For example, if the index column list is A1, A2, and A3, a query using A1 and A2 would be optimized using the index, but a query using A2 and A3 would not.
• Order in which the query accesses the columns in an ORDER BY clause matches the order of the column list defined in the index. (The query would not be optimized if its column list were A2, A1.)

Tip: If you expect to issue frequent queries against a table where the queries use the OR operator, it is better to create a single-column index for each condition. Since multi-column indices are sorted hierarchically, a query that is looking for any one of two or more conditions would, of course, have to search the whole table, losing the advantage of an index.