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/