# Indexing fields in "ORDER BY" clause

**URL:** <https://forums.percona.com/t/indexing-fields-in-order-by-clause/1151>\
**Category:** Other MySQL® Questions\
**Created:** [April 18, 2009, 2:28pm UTC](https://forums.percona.com/t/indexing-fields-in-order-by-clause/1151 "2009-04-18T14:28:55Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![pkiula](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/pkiula/32/808_2.png) [@pkiula](https://forums.percona.com/u/pkiula)\
**Post date:** [April 18, 2009, 2:28pm UTC](https://forums.percona.com/t/indexing-fields-in-order-by-clause/1151/1 "2009-04-18T14:28:55Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![sterin71](https://avatars.discourse-cdn.com/v4/letter/s/5e9695/32.png) [@sterin71](https://forums.percona.com/u/sterin71)\
**Post date:** [April 19, 2009, 2:07pm UTC](https://forums.percona.com/t/indexing-fields-in-order-by-clause/1151/2 "2009-04-19T14:07:06Z")

</div>

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.