Helen and Robert,

>>Why shouldn't I have triState indexed? In IBExpert the query seems to be
>>using the index if I use "IN (0,2)" but not if I use "!=1".

H> If your triState column is in fact a tri-state variable, then the
H> selectivity is impossibly low. This is a real killer if there's a chance
H> that the optimizer would use that index.

I think the point of this whole thread ceases to be relevant if the
column isn't indexed. Without an index, != and IN should take approx.
the same amount of time (due to the table scan).

I'd say that the usefulness of an index here depends on the value
distribution, the cardinality of the table and the type of filters
applied (search for common values vs search for rare values).

Since Robert is worried by the difference in performance between the
two statements, I guess the cardinality of the table is high enough to
warrant an index; if the selectivity of the index for the common
search values is low then raise it (adding a unique field to the
index), but definately keep the field indexed as it proves to be
useful.

Ciao
--
Nando mailto:nandod@...