Re: OnLine Optimizer with compound indices??

In article <3j2vdv$oa5@charnel.ecst.CSUChico.EDU>, tsaylor@ecst.csuchico.edu (Todd M. Saylor) says: >Now, I want to do a select such as: > >select a,b from my_table where a || b > "XXXX"> >(The value "XXXX" spans both column a and b). >This causes the optimizer to apply a filter as it reads *ALL* index >values (at least that seems to be the case, based on the long "think" >time before the first record is returned). However, if I were to just >say '... where a > "XX"', or something similar, Informix would quickly >jump to the correct value. Since the index is on (a,b), I don't see >why my example can't work the same way. > Informix can only use an index to aid as search where the index starts with the required fields e.g. an index on (a,b) would be if no use for '... where b = "ABC"' - it would have to scan the entire file. Presuming, therefore, that '||' equates to 'a > "XXXX" OR b > "XXX"', this means that your index on (a,b) is of no use for the second part of your query. You may get a better result from creating a second index on (b,a) and using a UNION. Try using 'SET EXPLAIN ON' if your are on a version of Informix which supports this. Hope this helps. Sally