Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 730
  • Last Modified:

Create index on "order by" parameter?

Hi,

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!
0
Monica2003
Asked:
Monica2003
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
0
 
nitinmehtaCommented:
well, it will certainly help, otherwise the tree traversal (or the cost in short) for sorting will be much more.
0
 
Monica2003Author Commented:
Should I create a composite index(intl, level, score) or two indexes: index(intl, level) and index(score)?  Thanks!
0
 
LowfatspreadCommented:
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)

 
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I agree with Lowfatspread that the composite index will give the best performance
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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