Jonathan,

J> I still don't quite understand why in my case, an index wouldn't make
J> things faster. Of course, if I want to select value 1,
J> the database will have to look through lots of records, and I understand
J> that this would be slower. But selecting value 0 should be much faster.

FWIW this is my understanding too.
I have always measured the selectivity of an index in
InterBase/Firebird against a particular predicate, rather than in absolute
terms. Unfortunately InterBase/Firebird store just a single
selectivity value (the mean value) so the optimizer hasn't got all the data it would
need to do that (a selectivity figure for each value in the index would be needed,
which wouldn't be feasible for indexes with a high number of values,
e. g. unique indexes). For this reason, it may
well be that an index is not selected even though your query would
benefit from it. I think this is the only real problem.
I would be glad to be corrected on this analisys, by the way.

J> If I have 20 000 records, of which 50 have state 0 and 19 950 have state
J> 1, then an index on the state column ought to make selecting value 0
J> much faster,
J> and selecting value 1 much slower. Isn't that right?

Again, I think so too.

Ciao
--
Nando mailto:nandod@...