|Subject||Re: [firebird-support] Composite index - issue or not existing feature?|
On Mon, Mar 14, 2016 at 5:30 PM, setysvar setysvar@... [firebird-support] <email@example.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.
(a) use XXX__A__B once using it exclusively for A
(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