Indexing fields in "ORDER BY" clause

  • Filter
  • Time
  • Show
Clear All
new posts

  • Indexing fields in "ORDER BY" clause

    Hi. Suppose I have a query like this:

    SELECT t.teams_id,t.user_id,t.tournament_id,t.teamname,u. user_id,u.fname,u.lname FROM ifc_teams t INNER JOIN users u on u.user_id=t.user_id ORDER BY t.teams_id DESC LIMIT 15;

    In this, I suppose it'll help to have indexes on:

    1 t.user_id
    2 u.user_id
    3 t.teams_id

    But for 1 and 3, should I have a compound index? Or does the ORDER BY clause expect an index by itself because it only uses one column?

  • #2
    You don't really need a compound index in this case since you don't have a condition your t table.

    The execution plan for this query will be that mysql uses the t.teams_id index to get the records from t in correct order and then use the u.user_id index to find the corresponding rows in the u table.

    If you had had a "WHERE t.xxx ORDER BY t.yyy " then a compound index on (xxx,yyy) will be beneficial.