Subject RE: [firebird-support] Optimal algorithm for inventory
Author unordained
> example:
> index (bDate, eDate);
> SELECT * FROM table WHERE :iDate>= bdate AND :iDate<=eDate;
> Firebird will navigate from begin of table to iDate by "bDate index" and
> eDate will test naturaly.
> If you are create only bDate index you will get same performance with less
> over head.

I see what you mean about compound indices; instead consider that FB builds a bitmap index before
retrieving rows -- so long as your query is built as an AND, it should be able to build two bitmap
indices based on birthdate and deathdate individually, AND the two together, and retrieve those
records only, without performing a full or even partial table scan. What would confuse it is the
use of (x and (y or z)) where it becomes less obvious that an index can be used on y and z -- but
splitting it into two statements, as a sum of selects or a union of selects (depending on if you
need count or content) should help it with the index selectivity issues on deathdate, making it
relevant and useful. I would expect that historically, birthdate and deathdate should have about
the same index selectivity -- both would show variation due to wars (deaths during and births
after), but come out about the same.