Re: Multi-Column Indexes, field order, and performance.

> Subject: Multi-Column Indexes, field order, and performance.
> From: "Mark Smith" <[email protected]>
> Date: Thu, 7 Sep 2006 10:49:27 -0700
> To: <[email protected]>
> 
> 
> Hello I have a question on using a Index that spans multiple fields
> INDEX(a,b,c).
> 
>  
> 
> If my queries, that use this index, all have where conditions that
> include all three fields; does it matter about the index order for query
> performance?
> 
>  
> 
> In other words is INDEX(a,b,c) = INDEX(c,b,a) or any combination there
> in?  Is it true for all index types or Databases for that matter (MySQL,
> Oracle, MSSQL)?
> 
>  
> 
> I am in a quandary because the last component in this case is time
> (field c).  I have been asked to aggregate across the time dimension for
> fields a and b.  Because now a and b are no longer conditions in the
> where statement (now included in the group by) using the existing index
> is no longer possible or feasible.  Because of legacy SQL code I am
> worried about simply changing the order at this time.  Adding a second
> index is frowned upon due to the huge nature of the table and the space
> requirements.

The index order matters.

Example: INDEX(A,B,C)

You can use that index to search on (A) or (on A and B) or (on A,B and
C), but you cannot use it to search on C. Given this flexibility,
organize the columns in the index in an order that will support the
widest range of queries. Remember that you can rarely afford to support
all possible indexes because of the overhead indexes add to DML
operations so make sure you pick the most effective set of indexes.

-----BEGIN PGP SIGNATURE-----
Comment: GPG Public Key: https://www.biglumber.com/x/web?qs=0x4A256EC8
Comment: GPG Public Key: http://www.lorztech.com/GPG.txt
Comment: Google, Skype, Yahoo ID: michaellouieloria

iQEVAwUBRQdiAbXBHi2y3jwfAQpXiAf/f7VRypjwWyGdPfQxxXNDWseGFhyYMXsF
NHRFA3PJ5EMH1CHl9zwqxCH9ztXHedAB0xECwbemXp4RhwBGKTR9p6RzPVGIehgu
KyA4sa+QgZP8FC1oGDH8aNwwW9pkmUbrL8nvZeCCIaCI27WJqocXoo8hVxnFDUaq
NG8YbAbZIzc9p+fRi1iOd8MAC9dmc0LDTbm9/J9d2MKjUlK4dIlh2ZUfNdAR5taf
MX8RA7cBgA/CE12YU9TkLU1Modmr/KgKO1Q6N5Swtd+ziqeoaeVs0TXffmrx9Y92
s9UkLdUJ2TtHaARrlFU9Ou9NWLSsy8yrTA4/Dw53a461sILfnwTLpQ==
=g/x7
-----END PGP SIGNATURE-----