Milan Babuskov wrote:

>
> In an application I'm currently developing I have a lot of parametrized
> queries like this:
>
> SELECT * FROM table1 t WHERE :somevalue BETWEEN t.field1 AND t.field2;
>
> I wonder what kind of index would be most effective for this. Compound
> index on (field1,field2)? or maybe separate index on both fields? Or
> maybe just index on first field? Or maybe something like ASC index on
> one field and DESC on another?
>
> Values found in field1 and field2 are not unique, but are not far from
> that either.

Don't worry about the ascending / descending part. Firebird is just
as good at picking out ranges of value in either direction.

That query decomposes in the compiler into :something >= field1 and
:something <= field2. You might think that a compound index would
let you apply both predicates, but it doesn't (or at least it didn't
when I was paying attention). The scan of an index has to be
continuous - it can't skip over intermediate entries. If you have
a compound index, Firebird will use only the first element in the
index.

So, subject to testing which should be pretty easy, I'd say two
ascending indexes, one for each field. You'll build larger than
necessary intermediate indexes, but when the indexes are combined,
you'll read fewer rows.

Cheers,

Ann