Re: Index: order of fields

In article <1104_951125428_at_way>,
  Jenda_at_Krynicky.cz (Jenda Krynicky) wrote:
> What is the recomended order of fields in an index?
> I think it does matter right?
>
> Suppose I have these three fields I want to include in an index :
> Country : 'C' or 'S'
> Year : 1993-2000
> AGB : 1500 - 1700 (approx ... it's a number of weeks since some date)
>
> Now is it better to build the index with this order or just the
oposite .. or some other?
>
> Thanks, Jenda
> http://Jenda.Krynicky.cz
>

In general there are two rules to use when ordering the columns:

1. When all columns will always be present order them with the most selective, i.e., columns with the most distinctive values first. This improves the efficiency of b-trieve balancing maintenance.
2. When many queries will only reference some of the columns then these columns need to be first in order for the index to be used OR you will need multiple indexes in the case where several different columns or combinations of columns out of the set are referenced together.

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Feb 21 2000 - 12:03:07 CST