Subject Re: [firebird-support] Composite index - issue or not existing feature?
Author setysvar
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).