Re: Performance issue

Hans S'derlund wrote: We are using Informix 7.2 Two questions, I hope anyone know something about it: 1) Does select max(colname) generate a full scan if colname is the primary key? 2) Does the order of the search criteras in a where clause inflict the performance? (where a=1 and b='aaa' and c=2) Technically if the column you apply the max function to is an indexed value, your query will perform an index path key-only scan. This does a sequential or parallel scan of the index leaf nodes and returns the value. This is the fastest type on retrieval as the data pages are never accessed. Your second question really depends how complex your query is. A simple single table select using all values of a compound index probably will not mind a mis-ordered where clause. When you start to have multiple table joins or the optimizer has several indexes to choose from then you need to set explain plan and review the output. With reguard to you second question it is far more important to have the the index defined with the columns in order of the most exclusive to least exclusive. This will lend itself to better performance. David Henseler