On Thu, 30 Aug 2012 09:34:48 -0000, "jon_neve" <[email protected]> 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
