Re: Indexing

John: The optimizer is a cost based optimizer and the order of items in the query will not matter unless you use optimizer directives. Now if the filter created by "a=? and b=? and c=? is not very selective, and the optimizer estimates that a large number of rows will be returned from this query, it will not use this index. ---jmiller John Shepherd wrote: > Greetings all, > Following some poor response times during recent tests of a new > application, I've taken a look at the SQL generated and have a simple > question for all you Informix gurus out there. If we have : > (i) a composite index on a table defined on columns (A,B,C) > (ii) a query filter reads "WHERE C=whatever AND B=whatever AND > A=whatever" > > ... is Informix smart enough to realise that the index in (i) should be > used ? Or is it best for the query to be coded in the precise order of > the index definition ? I've executed some EXPLAINs which seem to suggest > that Informix *is* smart enough, but I'm not entirely convinced by the > output from this tool. > > Thanks in advance for your help. Regards, > John

In any case don't forget to UPDATE STATISTICS HIGH on A or costs may not be calculated correctly. Roy G. John Miller wrote: > John: > > The optimizer is a cost based optimizer and the order of items > in the query will not matter unless you use optimizer directives. > Now if the filter created by "a=? and b=? and c=? is not very selective, > and the optimizer estimates that a large number of rows will be returned > from this query, it will not use this index. > > ---jmiller > > John Shepherd wrote: > > > Greetings all, > > Following some poor response times during recent tests of a new > > application, I've taken a look at the SQL generated and have a simple > > question for all you Informix gurus out there. If we have : > > (i) a composite index on a table defined on columns (A,B,C) > > (ii) a query filter reads "WHERE C=whatever AND B=whatever AND > > A=whatever" > > > > ... is Informix smart enough to realise that the index in (i) should be > > used ? Or is it best for the query to be coded in the precise order of > > the index definition ? I've executed some EXPLAINs which seem to suggest > > that Informix *is* smart enough, but I'm not entirely convinced by the > > output from this tool. > > > > Thanks in advance for your help. Regards, > > John