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).
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).
Think of XXX__A__B more as the index of a book, than a tree. You would
have to look up the "subchapter" of B under each "main chapter" of A.
I never run into this issue, simply because I always(*) prefer single
field indexes.
HTH (even though it may be overly simplified),
Set
(*) In theory with the exception of 'borderline performance issues',
were single field indexes are too slow, whereas multifield indexes are
quick enough. Though I have to say that I work on smaller databases than
you do and never have experienced this (I prefer simplicity over - let
me make a wild guess - 20% performance improvement).
