dr_bentonquest wrote:

> Strange, isn't it? The index is picked up when using "greater than
> zero", but it is NOT when using "different than zero" (<> symbol) or
> "not equal to zero" (not field_name=0) or with your suggestion
> (field_name+0 <> 0)
>
> I should add this: When using "<>0" and "not field_name=0" the proper
> index is NOT picked up, but another index is. In this cases, the
> optimizer chooses an index that contains only the first field,
> CUSTOMER, because this field is a FK on the table and a RDB$FOREIGN
> index exists for it.

Ah! that's the clue. The optimizer makes some assumptions about the
distribution of values in the index. One is that the majority of the
entries will be NOT any particular value. So it won't use an index
if the conjunct is <field> NE <value> or NOT (<field> EQ <value>).
If there were no other index, it would use the compound index because
it can use the first term, but searching a smaller index is faster
than searching a larger one, so CUSTOMER is favored over CUSTOMER +
some unusable term.


Regards,


Ann