Re: Set explain use

David Williams wrote: > > 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.... Nice thought, and that's how it used to work, but not for a long time. As Art says, it used to be a black art optimising queries by getting the joins the right way round, and putting the smallest tables first in the FROM clause, etc. <sigh>, them were the days. But now it's all done for you. But don't despair, I believe we are going BACK to the stone ages with version 7.3 or there abouts, with optimiser directives. x-| Cheers, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com FAQ http://www.iiug.org |///// / //| | +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| | Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////| |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| +----------------------+-----------------------------------+-----------+