Hello all,

I am wondering, is there a difference in performance between creating a multi-segment index (on two fields for example), and creating two separate indices (for the same two fields)? To illustrate my questions, consider the following query :

select *
from table1
where producer = :producer
and date >= :date_min and date < (:date_max + 1)

On a query like this I would often put an index on the date (usually a very good index in my experience), and perhaps on the producer, depending on how unique it may turn out to be.

What I'm wondering is whether or not an index on (date, producer) would be better in this situation than two separate indices, one for each field. I have a situation where the combination of the two fields should actually be unique, and so it should be a very good index.Would Firebird be able to combine the two separate indices in such a way as to be able to use them as efficiently as one double-segment index? The obvious drawback of the double-segment index being that it's less versatile, as it can only be used in queries where both fields are used as search criteria...

Thanks in advance,
Jonathan Neve.