Indexing fields in "ORDER BY" clause

[pkiula]

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?

[sterin71]

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.