On Thu, 30 Aug 2012 09:34:48 -0000, "jon_neve" <jn@...> wrote:

> 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...

I can't say what would perform better for this specific query given that
queries on ranges are always a bit more hairy than queries with specific
conditions. I would expect an index on (producer, date) to be better than
(date, producer) in this case as producer is probably the most specific
entry here as you try to select on a range of date.

The advantage of a multi-segment index is that you reduce the search space
for the second item in the index (you only need to search those entries
that match the first item). Searching two separate entries would require
something like scanning the first index and note all records that match,
then scanning second index and note all records that match and then
obtaining the intersection of the results of the first and second index
(NOTE: this is not necessarily how Firebird works).

Mark