On Mon, Mar 14, 2016 at 5:30 PM, setysvar setys...@gmail.com [firebird-support] <firebird-support@yahoogroups.com> wrote:
> > > Hi Karol! At the risk of this being confusing or even incorrect (I trust > Dmitry or Ann will correct if it is incorrect). > > >SELECT * FROM dbo.XXX X WHERE X.A BETWEEN 2 AND 30 *AND* X.B BETWEEN 5 > AND 60 > > My understanding of this is that Firebird (in theory) have two choices. > > Either > (a) use XXX__A__B once using it exclusively for A > or > (b) use XXX__A__B 29 times (2 through 30) using it for both A and B. > > Firebird prefers to use (a). > Err, not quite. There's no guarantee that there are only 29 values between 2 and 30. That depends on the datatype, which is not available to the index code. With the exception of 64-bit integers (long sad story), all numeric values in Firebird indexes are represented as mangled double precision floating point, so there could be 29*(2**56) values between 2 and 30. (Probably my math is off) And multiple instances of each. > > If you added a new index covering only B, then Firebird could use > XXX__A__B UNIQUE for A and also the new index for B (unlike many other > databases, Firebird can utilize several indexes for each table of a query) > Right. Cheers, Ann
