At 01:25 PM 19-08-01 +0200, you wrote:

>Just for me to remember, selectivity is (1 / count of unique values) ?
>And what does selectivity exactly mean and what it is used for ?

AFAIK, it is the count of unique values / count of values. So, in a table of 10,000 rows, the selectivity on your boolean column would be .0002, i.e. close to "no selectivity" (natural order), whilst its primary key's selectivity would be 1 (highest possible). Probably there are some other numbers around that represent how the optimizer calculates it, though.

The big problem in IB is that an index tree forms long chains of duplicates. So, where the count of unique values will be small and the table will be large, it's best to avoid them so that you don't waste time waiting for the engine to step through these long chains (potentially, all the way to end) before scoring a "hit". It is statistically more likely to score a "hit" by traversing the column in natural order.

If there are only two values, then your column will be 1 if it isn't 0. Therefore the engine doesn't need an index to help it decide the sort order...(YES - boom! NO - uh-huh. YES - boom! YES - boom! NO - uh-huh...) At the end, all the "non-hits" get tacked onto the end of the "hits" and you have your sort done.

I don't know if this helps...

cheers,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________