Hi Group,

I have a table with two fields that are indexed. One of these fields
is a foreign key supporting index, the other was defined.
Individually, both indices have poor selectivity, 100 - 1000
duplicates per note. Combined however, these fields are nearly unique
(not quite, there is nothing in the business rules that says that two
records can not have identical values, but in practice it rarely has
duplicates).

select count(*)
from table
where fieldA = 1

Between 100 and 1000.

select count(*)
from table
where fieldB = 1

Between 200 and 300

select count(*)
from table
where fieldA = 1
and fieldB = 1

Between 0 and 3 records, mostly 0 or 1.

1. Given that Firebird is capable of combining both of those indices,
is there any benefit to creating a compound key across them?

2. If it is simple to explain, the optimiser must make a cost
estimation of the this bitmap, so how is it done (rough description is
fine)?

3. If the answer to 1 is that there is no benefit, is there any reason
to ever use a compound index?

Thanks

Adam