Composite index

If I have a composite index, I assume that the best performance gain would be to create the index in order of column size - smallest column to biggest column Create index i1_index on tab1 (col1, col2, col3) Where col1 is the smallest, and col3 the largest. Is my assumption correct ? Dirk Moolman Database and Unix Administrator HEALTHCORP "People demand freedom of speech as a compensation for the freedom of thought which they seldom use." sending to informix-list

Dirk Moolman wrote: > If I have a composite index, I assume that the best performance gain > would be to create the index in order of column size - smallest column > to biggest column > > Create index i1_index on tab1 (col1, col2, col3)> > Where col1 is the smallest, and col3 the largest. > > Is my assumption correct ? Dirk, Keith's nailed it. Put the most selective columns first in the key followed by key columns of lesser filter value. This is of course ignoring the possible value of the order of key columns in indexes to eliminate sorting. However, when both requirements exist you may want multiple versions of the same key column set in different orders. That will add to the overhead of inserts/updates/deletes, but the improvement to query speed may be worth the cost to data maintenance. Art S. Kagel