Sometime last week Stephane Vander Clock mentioned that for his application,
compound indexes were much more efficient than combining indexes on the
individual columns. His database has several columns with really poor
selectivity -
how many houses have fewer than one or more than 10 bedrooms? - so the cost
of building the bitmap of matching rows on each of those columns is significant.
On the other hand, compound indexes don't work if the first columns are selected
on a range, rather than an equality match.

However, internally, Firebird could do a very fast lookup on the
combination of a
record number and a value, even if the selectivity of the index is
poor based on the
key.

Could Firebird add another indexed record selection mechanism that used both the
value and the record number? Then a join with one moderately selective indexed
term and one other indexed term with poor selectivity would be done like this:

1) Develop the bitmap of records that match the moderately selective criteria -
something like properties in New York City that are not in Queens or
Staten Island.

2) For each record in that bitmap, dive into the second index using
the value from
the restriction term and the record number with from the bitmap.
That's a unique
key. If it's found, keep that record number in the primary bitmap, if
not, drop it.

Obviously if there's more than one secondary index that could be used, use it
the same way. So you start with all the records numbers in New York City,
drop out all the ones that cost less than $500,000 or more than $1,500,000,
drop out all the ones that have fewer than 3 or more than 6 bedrooms, etc.


The same mechanism could be used with navigational access on the first index
for queries that use the FIRST... mechanism.

Cheers,

Ann