Re: Ref: Performance

Jack Parker (jparker@hpbs3645.boi.hp.com) wrote: : However, I feel that you missed something. Adding columns to an index is : fine and good if you will be querying with values for the fields from that : index. But adding column XYZ to an index does not mean that a query against : the table in question will start using XYZ by itself. (feel free to correct : me anyone) but unless you are using the first part of the composite index in : the read - any additional part will not be used in the query. I do not know : if an index such as: : idx_1 (col_1, col_2, col_3, col_4) : will be used when querying with values for col_1 and col_4. It WILL be : used at a minimum (unless there is another index on col_1) for col_1, but : I am unsure whether the query will benefit from col_4 in the index. Jack, Good point, I did miss something by sending out part of a thread that I had already been involved in. In the precursor work to the posting, I had identified that adding the additional fields to the index would allow the optimizer to satisfy the query with a Key-Only index search, optimizing my query time by a factor of ten. The query looked like this: select fieldone, fieldtwo, fieldthree, fieldfour from table where fieldone="ABC" and fieldtwo="EFG" The original index was table(fieldone,fieldtwo) and the SET EXPLAIN showed that it was using the index and then going to to the data pages because it needed fieldthree and fieldfour. Adding fieldthree and fieldfour to the index allowed everything to come from the index. This was especially important since table was very wide (about 500 bytes in 73 fields), [yes, I have heard of normalization, but it's a new concept to some of my developers :) ] The new index did everything that the old index did and optimized this one very important select at a minimal cost. Joe "Bubba" Lumbley -- =========================================================================== jlumbley@netcom.com (Joe Lumbley) BancTec Service Corportation 214-450-9896 Dallas, Texas Watch for my _INFORMIX DBA SURVIVAL GUIDE_ in Fall '94 from Prentice Hall! ===========================================================================