Re: Composite index

Hi, I'm not so sure about this. The keys of an index are stored on the index pages themselves. So in general there's no extra memory access necessary to get the keys. If at all, I'd be tempted to look at the effort of comparing keys rather than key size. These two things may somehow be connected. But there can be specific scenarios, e.g. a 4-byte integer being a machine word is probably easier to compare than a 3-byte localized character column value ... But more subtle scenarios are implementation detail and we don't really know (may be subject to change also). The other thing to keep in mind is that a composite index can be used by different queries, i.e. with your example the index can be used by queries needing col1 only, or queries needing col1 and col2, etc. But it cannot be used for a query needing col3 and col2 (without col1). For the latter you need a new index with ...(col3, col2). Probably I would try to optimize indexes so that they can be used by as many queries of my application(s) as possible, ideally saving me a couple of extra indexes ... Regards, Martin -- Martin Fuerderer IBM Informix Development Munich, Germany Information Management owner-informix-list@iiug.org wrote on 06.05.2005 11:42:36: > 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 sending to informix-list