Re: optimizing the sequence of index fields

In article <5j8omc$d4t@nntpb.cb.lucent.com>, sicherman@lucent.com (Midnight Sunburst) wrote: >We're having poor performance when tracing is turned on for an index >whose first field has thousands of identical values. > >It seems as if a multiple-field index will be more efficient >if the values of the first field are highly varied - but I never >can tell with Informix! Is there a reliable way to sequence the >fields in an index for efficiency? > Indexes have to be sequenced based on how you plan to use them, irrespective of whether you are working in Informix or any other of the (lesser:)) databases. Sure, it would make sense to keep the most unique field of a composite index at the beginning. But it wouldn't always be the best thing to do (though it usually is) For example, say I have a table with columns a,b,c,..... b is the most unique, a is half as unique. However, whenever I want to query on b, I always know a. Sometimes I want to query on a alone. The sensible index is (a,b). HTH. ----------------------- Rudy Fernandes GIC, Kuwait OL 7.20UC4, 4GL 6.04UC1 -----------------------