Re: Set explain use

In article <64v7a5$isc@cssun.mathcs.emory.edu>, kagel@bloomberg.com writes > >Danni Bauer wrote: >} >} satriguy@aol.com (SaTriGuy) wrote: >} >} >>Not considering indexes, does it make any difference to the informix >} >>optimizer >} >> if we switch the order of conditions in a where clause. For example, which >} >>one >} >> is better: >} >> >} >>select * from A where p = q and r = s or >} >>select * from A where r = s and p = q . >} >> >} >>None of p,q,r, and s is indexed. >} > >} >Nope - not this this query. The optimizer will treat an "and" condition as >an >} > "equiviliant" and will consider both when creating the query plan. >} >Madison Pruet >} >} It should not matter. But, a couple of years ago I found out that it >} did! After all else failed, I just tried flipping them and BANG! my >} performance problem was solved. I was using 5.0? I think. > >That sounds like the behavior of the 4.xx optimizer which was syntax >based. I have a lengthy document, written by Tony DeCeccio, detailing >how to help that bugger out by adjusting the order of join and filter >conditions. I loved playing with that optimizer. > >Art S. Kagel > > > I thought that the order made a difference!! I've always had the idea that when you write a query you think about performance anyway (you do create indexes right?) therefore you have already thought about the 'ideal' way the query should be executed ...use this index on tab1.col1 then join using tabl1.col2 to tab2.col2 to this table using this index...etc.. so why not generate the query like that:- "from tab1,tab2 where tab1.col1 = "XX" and tabl1.col2 = tab2.col2".. You've done most of the work designing the tables and indexes, you have to generate the sql anyway why not generate it in the 'ideal' way? Then you can be reasonably sure that the optimizer will consider the 'obvious' way to execute the query and since you know that will be the fastest.... -- David Williams