Re: ANY IDEA ON USING INDEX FOR ORDER BY

Bill Ennis wrote: > > 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? Several points. If you have 7.24 you can SET OPTIMIZATION FIRST_ROWS which will change the optimizers goal from reducing total cost to getting the first rows as quickly as possible. This will tend to favor the index that matches the ORDER BY clause. However, as someone else has mentioned, the optimizer usually chooses the best plan if you need to fetch all of the matching rows it is just that if a physical sort is needed then the sort has to wait for all of the data to be fetched before the sort can begin so the first FETCH will hang until then. In our testing I have a similar situation where one table which 5.0 used to used index#3 for because it matched the ORDER BY 7.21 uses index#1. Since we needed the first 18 rows ASAP this was a problem as 5.0 FETCHED the first 18 rows in <2 seconds while 7.21 needed 13.5 secs to return the first 18 rows. Further testing determined that if all of the few thousand matching rows were fetched the total query took only 14 seconds to return all rows. If we dropped index#1 then index#2 was chosen returning the first 18 rows in 14.5 seconds and all rows in about 15 seconds. If we then dropped index#2 also: finally index#3 was used and the first 18 rows were returned in <1 second (faster than 5.07!) but the total query took > 40 seconds! Obviously the optimizer was performing its stated function to reduce total cost. We presented this to Informix and asked for a SET OPTIMIZATION FIRST_ROWS option (actually I called it SET OPTIMIZER GOAL FIRST FETCH) and voila this option was added to the plan for 7.3 and tested in 7.24! It was QA'd in 7.24 in November and is now a supported feature in 7.24. I get 7.24 next week, the port to our platform was just completed, and I cannot wait to add back all those indexes I needed to drop! So, depending on your goal you may or may not want to use this feature or drop indexes. Art S. Kagel