Re: ANY IDEA ON USING INDEX FOR ORDER BY

Hi, David cites an interesting point. And I actually did some experimentation with this a little over a year ago. I was finding that after about 3000 rows that the index for the order by started to pay off. Sorry I didn'y record the percentage of rows in the test table (in hindsight I'd guess this was between 5-10% of the table I was using). FYI - the way I would "force" the index I wanted was by disabling the one I wanted to eliminate from the optimizer selection. Has anyone else experinted with query performance on a select with an ORDER BY using indexes that can be used to satisfy an ORDER BY vs. using a fairly using key to dearch with? If so what percentage did you see as the turning point? -Bill } } 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 -- Bill Ennis Voice: 312-474-7516 SSA Fax: 312-474-7460 500 W. Madison emails: ennis@ssax.com ennis@starnetusa.com