Create index on "order by" parameter?

Posted on 2007-07-20
Last Modified: 2012-06-22

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!
Question by:Monica2003
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    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
    LVL 4

    Expert Comment

    well, it will certainly help, otherwise the tree traversal (or the cost in short) for sorting will be much more.

    Author Comment

    Should I create a composite index(intl, level, score) or two indexes: index(intl, level) and index(score)?  Thanks!
    LVL 50

    Accepted Solution

    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)

    LVL 142

    Expert Comment

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

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
    Creating and Managing Databases with phpMyAdmin in cPanel.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now