On Mon, Mar 14, 2016 at 5:30 PM, setysvar setysvar@... [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