RE: Which index is fastest

table A(col1 serial, col2 char(1)); inserted 100,000 rows with col2="A" inserted 100,000 rows with col2="B" I looked into sysindexes after an upd. stat. index_A (col1,col2) has levels=3 , leaves=1552 ,nunique=200,000 , clust=893 index_b (col2,col1) has levels=3 , leaves=1551 , nunique=2 , clust=1786 Why does the number of leaves differ ? What does clust denote ? Pl. explain. TIA Sathish Sadagopan -----Original Message----- From: dkosenko@monmouth.com [mailto:dkosenko@monmouth.com] Sent: Tuesday, March 09, 1999 9:39 PM To: informix-list@iiug.org Subject: Re: Which index is fastest :On Mon, 08 Mar 1999 13:50:26 -0500, "Art S. Kagel" :<kagel@bloomberg.net> wrote: : :>Demetrios Stavrinos wrote: :>> :>> For whatever is worth! :>> We use composite indices extensively. The best results are achieved with :>> whatever combination makes the COMPOSITE index more unique! :>[SNIP] :>Correct but the original post listed the same columns for each index :>just in different order. The uniqueness of the indexes will be :>identical with the same number of nodes. HOWEVER, the index that :>begins with the more unique column will have fewer levels and therefore :>be slightly more efficient. :> :>Art S. Kagel The number of levels will not be affected since the total number of key values remains the same (remember that the key value is the concatenation of all the bytes in the columns indexed). What will change is the distribution of those key values across the nodes. When doing partial key searches, having the more unique column(s) first in the index is beneficial. If you always use the entire key value, it really doesn't matter that much. Dave Dave Kosenko (posting from home) Currently teaching folks everything I know about Informix at Summit Data Group (an Informix Authorized Education Center) For more info, see http://www.summitdata.com ********** "Everybody plays the fool, sometimes."

Sadagopan, Sathish, CFCTR wrote: > > table A(col1 serial, col2 char(1)); > inserted 100,000 rows with col2="A" > inserted 100,000 rows with col2="B" > > I looked into sysindexes after an upd. stat. > index_A (col1,col2) has levels=3 , leaves=1552 ,nunique=200,000 , > clust=893 > index_b (col2,col1) has levels=3 , leaves=1551 , nunique=2 , clust=1786 > Why does the number of leaves differ ? > What does clust denote ? Pl. explain. The field nunique is only updated when you run UPDATE STATISTICS LOW or HIGH (without the DISTRIBUTIONS ONLY option so it includes LOW). The field clust is only relevant to a clustered index and is supposed to indicate the degree to which the table is still clustered as new rows are added it is also maintained by UPDATE STATISTICS LOW. If you have not updated stats lately these values are meaningless. Art S. Kagel