Re: Creating indexes

---- junet@informix.com (65) 298-1716 ---- Responded: >>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. Both true. The point made above (A,B,C) refers to indices made with columns that are at the head of the list. Five columns shows this ideally (IMHO). If an index on (c1, c2, c3, c4, c5) exists, then you can have situations where an index on (c1, c5) or (c3, c4) will be of great benefit. However the cost of maintaining an index on (c1, c2, c3) as well as (c1, c2, c3, c4, c5) is very, very difficult to justify. Paul Kapp ----> PLKapp@aol.com