Re: Which index is fastest

So, according to Art's, the best index from my latest example would be (gender, zip) instead of (zip, gender), wouldn't it ? 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 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 -----------------------------------------------------

: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."