Database indexes and combinations

Question

I've read in several places that you make any field used to restrict the results of a query into an index. But suppose you have an application in which different queries use different combination of columns.

For example, let's suppose we have three queries as follows:

1. Select * from table1 where field1 = 'A';
2. Select * from table1 where field1 = 'A' and field2 = 'B';
3. Select * from table1 where field1 = 'A' and field3 = 'C';

Is it better to create one index for field1, one field2, and one for field3? Or is it better to create an index for field1, one for the combination of field1 and field2, and one for the combination of field1 and field3.

Answer 1

An index on field1 will help with all three queries. A composite index on either (field1, field2) or (field1, field3) would help the first query and either the second or third — and renders a non-unique index on field1 redundant (since the composite index can be used with the first query).

For any given index, you have to weigh the cost of adding the index against the benefit of having the index. The index uses space; the index uses processing time when rows are added, deleted or updated (in general). If you're going to use the index often enough and it provides sufficient speed-up when it is used, then the benefit outweighs the cost. If you seldom use the index, it may not pay for itself. If the table is dynamic (rows changing frequently), the update cost of the indexes is greater than if the table is relatively static (seldom changes).

Although the optimizer has to work harder when there are more indexes (checking which ones are relevant), it is seldom the determining factor in a decision to create or not create indexes.

Generally, make sure you have all unique indexes that are relevant on the table.

Add composite indexes if you are confident (because you've measured) that they will speed things up by a large enough margin, and will be used often enough, to give you an overall benefit.