On Thu, Aug 30, 2012 at 5:34 AM, jon_neve <[email protected]> wrote: > > 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)?
The straight forward answer is that creating one multi-segment index requires reading and sorting the table once, while creating two single segment indexes requires reading and sorting the table twice. But you're really not interested in the cost of creating an index, are you? > 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... > > Normally when a query has conditions on fields in more than one index, Firebird reads one index, setting bits in a bitmap for each record number from the index that has a record that satisfies that condition, then reads then next, also setting bits. When it runs out of indexes, it combines the bitmaps and uses the result to determine which records to read. So, if the combination of producer and date is unique, combining the bitmaps from the producer index and the date index will result in a bitmap with one bit set - or none if there's no such record. If you always (or very often) use both producer and date and know that you never have two records from the same producer on the same date, then a combined unique index has the advantage that Firebird's optimizer will know that it only needs to look at that index to produce at most one record. Generally, I recommend separate indexes. They're useful in more cases. Good luck, Ann (The exception to the bitmap index lookup is a query that includes FIRST or LIMIT where the ORDER BY terms are indexed. In that case, Firebird will walk the index to get the rows in order) [Non-text portions of this message have been removed]
