Re: Informix 5, greater thans, and cpu usage

> The output of explain says that it is using the index to satisfy all the > parts of the query, but also that it is using a temp table to do the > order by. This adds to my suspicion about a sort taking place. > so, my questions are: > > Shouldn't Informix not need to do a sort, if the rows returned in > index order are the correct order for the ORDER BY? That depends on how intelligent you expect the optimiser to be!! For a start depending on which half of the query the engine does first it might not come out with the correct order. Even supposing that it does the two halfs in the order obvious to humans you are expecting it to know that the two halfs are close enough together to warrant reading the index in sequence. For example if your values were 1 " " 1 "A " 1 "AA " 14,995 rows later 2 " " Is it sensible to scan the entire index to pick up 2 records in the correct order when seperate lookups and a sort should be faster. At the moment the optimiser has neither the information nor the intelligence to make this judgement call so it does the best it can. In this case I wouldn't have been surprised by a sequential scan. You appear to be lucky that for some reason it believes it can use the index to improve matters. Sorting afterwards is fairly standard with queries of this kind because of the above problems. > If indeed Informix is performing this sort, would that peg my cpu > like I am observing? Sorting does take cpu power but so does scanning 15,000 records so I would expect its a combination. > Is there a way to rewrite or otherwise optimize this type of query? You won't get rid of the sort but you may improve performance by splitting the select in half and using a union. This forces the engine to do a separate sort but depending on the spread of your data may be faster than the full index scan it is currently doing. Also OR has never been very efficient in the optimiser if applicaple UNION is almost always faster. E.g. SELECT rowid, cset, cacc, serialcolumn FROM gca WHERE ((CSET = "1") AND (CACC >= " ")) union SELECT rowid, cset, cacc, serialcolumn FROM gca WHERE ((CSET > "1") ORDER BY 1, 2 Used on a data set like my example above should beat the index scan hands down. > Gerry Tynen Hope this helps - Jim My opinions are my own. They may vary with time but they remain MINE! ---------------------------------------------------------------------- Name: Jim Gordon Internet: jgordon@ssf-sys.DHL.COM Company: DHL Systems Inc Phone: (415) 375-5222 (Work) Address: 700 Airport Blvd. #300 (415) 775-7762 (Home) Burlingame, CA 94010-1937 Fax: (415) 375-5019 ----------------------------------------------------------------------