Re: ANY IDEA ON USING INDEX FOR ORDER BY

In article <69laqg$dki@cssun.mathcs.emory.edu>, Bill Ennis <ennis@ssax.com> writes >Hi, > >Since you have an index on fileid the optimizer is chossing it because >it thinks it will be less work to scan it and do the order by afterwards. >i.e. the optimizer has at least 2 choices here: >1) Read the order by index (refno) first so it doesn't have to sort later > and then use weed out the non-matching records. >2) Scan the refno index and sort the returned rows. > >It appears the the optimizer feels that there aren't too many rows that >are between the range specified. What level of update stats are you running? >If you are not running high on the indexed columns you may want to try >hat. > >Another idea: Add the fileid to the order by index. > >Let me know how it goes, >Bill > Sound advice. >} >} ANY IDEA ON USING INDEX FOR ORDER BY >} I bet you have read a book/manual which recommends this. THIS ONLY APPLYS WHEN YOU SELECT MOST OF THE ROWS FROM A TABLE. Informix can only use one index at a time during a query. Generally the one index will the the best one for evaluating the where clause and finding matching rows rather then the one for the order by. This is reduce disk I/O and hence increase performance (Sorting a small %age of rows is faster than reading all the rows via the 'sorting' index and then finding the data pages to allow you to evaluate the where clause. Ever noticed that the examples for using an index for an order by never have a where clause? Most unrealistic. > >} The optimizer dos not use index on refno for order by when I add >} t.fileid BETWEEN "BK7180IN" AND "BK7200IN" in where clause. >} I tried update statistics but nothing changed. >} >} fileid CHAR(8) >} refno INTEGER >} >} And sqexplain.out is : >} >} QUERY: >} ------ >} SELECT * FROM transaction t >} WHERE t.fileid BETWEEN "BK7180IN" AND "BK7200IN" >} ORDER BY t.refno >} >} Estimated Cost: 3 >} Estimated # of Rows Returned: 1 >} Temporary Files Required For: Order By >} >} 1) crx.t: INDEX PATH >} >} (1) Index Keys: fileid >} Lower Index Filter: crx.t.fileid >= 'BK7180IN' >} Upper Index Filter: crx.t.fileid <= 'BK7200IN' > > >-- >Bill Ennis Voice: 312-474-7516 >SSA Fax: 312-474-7460 >500 W. Madison email: ennis@ssax.com ennis@accesschicago.net -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care