I have a table with 4 columns C1,C2,C3,C4

Sometimes there are selects with WHERE C1= xxx and C2 =kkk

and sometimes with WHERE C3= yyy and C4 =zzz


Is it better to have one index with all four columns C1,C2,C3,C4
or is it better to have two indexes, one with C1 and C2 and a second one wit
C3 and C4


Thanks for any ideas
Peter

Your single index on c1,c2,c3,c4 cannot be used for your second where
clause. To optimize these two specific queries, you should have two
indexes, one on c1,c2 and another on c3,c4.


--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

"Peter Stojkovic" <Peter.Stojkovic (AT) gmx (DOT) net> wrote

The column order in an index is critical to its usefulness.

An index on C1,C2,C3,C4 will be *useless* for a query on C3 and C4
since the primary sort order will be on C1 and C2. It may be useful
for a query on C1 and C2 but the presence of C3 and C4 are a waste of
index space.

The choices are...

C1,C2 versus C2,C1 versus just C1 versus just C2

and

C3,C4 versus C4,C3 versus just C3 versus just C4

Which one from each set of 4 you pick depends on the selectivity of
the various columns; i.e., if A = xxx matches fewer rows than B = yyy,
then A is probably a better candidate for an index, or at least a
better candidate for being the first column in an index.

(Now, watch me be wrong... Anil? Ivan? Glenn?

Breck


On 17 Dec 2003 08:10:23 -0800, "Peter Stojkovic"
<Peter.Stojkovic (AT) gmx (DOT) net> wrote:

--
bcarter (AT) risingroad (DOT) com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com