Re: order by and index coulmn

One more thing..... If you do not have a usable index on the ORDER BY columns , then server may require to store the results in temperory table and sort them afterwards. Thats the reason you may see Temperory Files used for Order By in sqexplain.out. If you have a usable index on columns same as the order of columns given in Order By clause , the optimizer will chose that index , so that means , server does not have to store results in temp table and then sort it , the index lookup itself is in ascending order which can also improve the performance of the query... Thanks Samar --- Jonathan Leffler <jleffler@earthlink.net> wrote: > Ken Hu wrote: > > Should I create index on a column that is used by > "order by" oper[a]tion? > > Does it help? > > I am not sure the answer, so please share your > thinking or opinion with me. > > It depends. > > First of all, it is not sufficient that the column > is just used in an > ORDER BY clause; the index has to reflect exactly > the columns of the > entire ORDER BY clause in the correct sequence. > > That is, if the query includes "ORDER BY col01, > col02, col03, col04", > then creating an index on col03 is a monstrous > irrelevancy, > but creating an index on col01, col02, col03 and > col04 (in that > sequence) might buy you some performance benefit. > > Secondly, you'd better be sure that the index is > really beneficial. > More typically, the optimizer will choose some set > of columns related > to filtering the data (WHERE clause) and then sort > the results - and > it will usually be correct. An index for ORDER BY > clauses has to be a > totally dominant factor in the system performance, > and personally I > doubt that it often is a major benefit, but bear in > mind that the > 'big' databases I play with occasionally reach 20 > MB, so my > perspective is probably a bit skewed. > > Track the performance of your queries with and > without the ORDER BY > clauses, and with and without the index in place. > Make your mind up > based on your empirical evidence. Don't forget to > run UPDATE > STATISTICS appropriately. > > -- > Jonathan Leffler #include > <disclaimer.h> > Email: jleffler@earthlink.net, jleffler@us.ibm.com > Guardian of DBD::Informix v2003.04 -- > http://dbi.perl.org/ > __________________________________ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ sending to informix-list