• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 732
  • Last Modified:

Create index on "order by" parameter?


I know that you should create indexes on the "where" parameters, but I was wondering if you should create indexes on the "order by" paramters.  For example, in the query below, should I create an index on the "score" field?

select * from table where intl='us' and level='2' order by score;

Thanks for your help!
1 Solution
Aneesh RetnakaranDatabase AdministratorCommented:
if this query is called so many times in an hour, then , creating the index is not a bad option. You should not that, indexes wont always improves performance, it can only improves the perfromance during the retrieval operation
well, it will certainly help, otherwise the tree traversal (or the cost in short) for sorting will be much more.
Monica2003Author Commented:
Should I create a composite index(intl, level, score) or two indexes: index(intl, level) and index(score)?  Thanks!
a composite index would geive the best perofrmance for the query....

however what is the primary key...
what is the clustering index?
what other ways do you access this table?

what indexes do you already have?
what is the size of the table?
what is the cardinality of the columns (how many distinct values do each of the columns have?)

(level,intl,score   maybe better)

Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree with Lowfatspread that the composite index will give the best performance
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now