Re: Creating indexes

>> When you create an index consisting of i.e. (date,number), is the order >> (date, number) or (number, date) of importance ? >> >> What if there are more elements in the index ? > >Rules of thumb: >1. If you expect to use some part(s) of a compound index without the other(s) > in a WHERE clause, put the parts to be used alone at the front of the index. > Informix can use leading (but not trailing) columns of a compound index > as if they were a separate index. Also, if any parts are to be used in an inequality in your query, they should go last. eg. SELECT ... WHERE c1 = a AND c2 = b AND c3 > 10 Make sure c3 is after c1 & c2 in your index. Anything after c3 in the index cannot be used. >2. For the reason stated above, do not have an index on (A,B) if you already > have an index on (A,B,C). You will pay the cost to maintain the (A,B) > index, but derive no additional benefit. Well, that's not completely true. Sometimes the size of the index (many columns) will cause it not to be chosen, where a smaller index would be. For example, if you have an index on (c1, c2, c3, c4, c5), and one on (c5), and you do a query using c1 and c5, you may choose the c5 index, although your performance might be greatly improved by using an index on (c1, c5) or even just on (c1) (eg if c5 is highly duplicate). It's tough to make any hard and fast rules on this. June ---- June Tong Informix Asia/Pacific ---- ---- On-Loan Engineer Singapore ---- ---- Location-du-jour: Bangkok ---- ---- junet@informix.com (65) 298-1716 ----