Hi Andrew,

I'll start by answering your second question since that is simple:

>Since FieldD and E aren't always present (they are Dynamic Queries,
>it depends on the user defined conditions before executing the
>query). Should it be (FieldA, FieldB, FieldC) and the other fields
>on other indexes?

No, one index with all fields will suffice. Firebird (and Interbase) can use the beginning of the index if necessary, and the index (FieldA, FieldB, FieldC, FieldD, FieldE) can be used if FieldA is specified and becomes more selective if FieldB, FieldC... is specified. Note that the order matters, if you haven't specified FieldA, the index is useless even if you now the other four fields (it's like looking in a telephone directory when you only know the last few letters of the surname).

For your first question you're simply asking the wrong question(s).
An index ought to be as selective as possible. Don't bother making an index on a field that has only two possible values or if 70% of the records have the same value in that field. Your "best" index would be one that contains all fields of your where clause where you check for equality or between a limited range (I'd go for having the most selective field first in your index, but I don't know if this matters). If the potential number of records matching the where clause is large, you may benefit from adding the primary key to the end of the index (it matters when deleting records).

If your design is good, there is no need for an index on FieldF - the number of records to sort should be few.

Did this help you anything?
Set