Re: Which index is fastest
It depends on data distribuition across gender and zip.
But (zip,gender) will be faster in a general case
(where gender has much fewer distinct values). The only case
when (gender,zip) could compete with (zip,gender) is when
you have the data just for one (or two) states in the table,
or when you are not filtering zip.
Kind Regards,
Octav
On Fri, Mar 05, 1999 at 09:49:19AM +0000, Manel Falc' wrote:
> So, if you have such a table:
>
> people
> (name,
> gender (F,M),
> zip)
>
> and you usually search:
>
> select * from people
> where gender = "F" and zip = "CA">
> Which index would be better ?
>
> (gender, zip) or (zip, gender)
>
>
> On 3 Mar 1999 17:17:16 -0600, hause011@garnet.tc.umn.edu (Steven
> Hauser) wrote:
>
> >Choose the field with the largest number of values in its domain
> >with the best even distribution over that domain to be the
> >'head' of the index.
> >
> >For most RDMS the 'head' (first value) of the index is the most
> >important in a composit index.
>
>
> Thanks a lot,
>
> Manel Falc'
> SEMIC, S.A.
> Lleida-Catalonia-Spain-Europe
> -----------------------------------------------------
> Informix version: Informix SE 5.07 / RDS 4.x & 4J's
> Operating system: Unix SCO V 5.04
> -----------------------------------------------------
--
Octav Chiriac Phone: (373) 2 21 20 96
NetInfo S.R.L. Fax: (373) 2 21 36 59
Chisinau (373) 2 24 00 83
Moldova, Republic of mailto:com@netinfo-moldova.com