Re: Explain this

On 23 Oct 98, at 0:00, Larry Foote wrote: > Sometimes the Informix "optimizer" baffles me. :-) > The following is the sqexplain file created upon running 3 slightly > different queries which all return the same single row. The first query > takes almost 2 minutes to return, while the other 2 return almost > instantaneously. The optimizer has chosen the wrong key, but why? > > There is only 1 character difference between the first and second query > and in the third query I just dropped the order by. <clutches straws> I suspect that based on the statistics the optimiser has to hand, it thinks that the number of unique values in the fname column would be the better index to work on. But I have to agree, given a matches and an equals, I would have thought the equals would have been a better choice all the time. Perhaps you could try an UPDATE STATISTICS, or several different ones? > Environment: INFORMIX-OnLine Version 5.10.UC1 on SCO UNIX 5.0.4 > > QUERY: > ------ > select fname,findex from folder where fmatter='22188.04000' and> fname like '0001%' order by fname > > Estimated Cost: 1 > Estimated # of Rows Returned: 1 > > 1) informix.folder: INDEX PATH > > Filters: informix.folder.fmatter = '22188.04000' > > (1) Index Keys: fname findex > Lower Index Filter: informix.folder.fname LIKE '0001%' > > QUERY: > ------ > select fname,findex from folder where fmatter='22188.04000' and> fname like '000%' order by fname > > Estimated Cost: 3 > Estimated # of Rows Returned: 1 > Temporary Files Required For: Order By > > 1) informix.folder: INDEX PATH > > (1) Index Keys: fmatter fname findex (Key-Only) > Lower Index Filter: (informix.folder.fmatter = '22188.04000' AND > informix.folder.fname LIKE '000%' ) > > QUERY: > ------ > select fname,findex from folder where fmatter='22188.04000' and> fname like '0001%' > > Estimated Cost: 1 > Estimated # of Rows Returned: 1 > > 1) informix.folder: INDEX PATH > > (1) Index Keys: fmatter fname findex (Key-Only) > Lower Index Filter: (informix.folder.fmatter = '22188.04000' AND > informix.folder.fname LIKE '0001%' ) > > <===========================> > Larry Foote > Footeware Software Systems > > > -- Ciao, Billy /Group Managing Director, The West Solutions Group: http://www.west.co.za \\ Drivel @ http://www.west.co.za/tasteless/ / \\ "Granted, Mr Wheeler's ideas are stupid and unreasonable, but he does own the company and I / think we should go along with him..." \\________________________________________________________________________________________________