Re: ANY IDEA ON USING INDEX FOR ORDER BY

Peter Tashkoff wrote: } Hello Gokhan } I am reading between the lines a bit, because the information you have posted is a bit sketchy, so bear with me if I am wrong. } } Are you saying that this table has an indexes as follows. } } scenario 1. } Index1 } Duplicates allowed (fileid) } } Index2 } (fileid,refno) } } Are you saying you want it to use the 2nd index, but it is using the first. } If so, you have company, I struck this problem last week in 7.23. } Firstly, the first index can almost be considered redundant , as the second index can give nearly as fast access for any query selected just on fileid. } Secondly, in 7.23 all of a sudden, this became a problem for us. } Somehow the optimiser is not taking the cost of the order by into account correctly. Our } <snip> Hi-Pete, Do you think the optimizer is the one taking you for this wild ride, then set your config-file with OPTCOMPIND 0 # To hint the optimizer If it is set to 1 or 2 the optimer will try its best give you better performance, but for us it was a waist of time. So we decided to set OPTCOMPIND to 0. If I do a query on our tables, it uses the index-key similar to your file-id. -- Have a nice day Felix K. Mathews mailto:fmathews@systems.dhl.com