Re: Indexing ORDER BY Columns

[James Day]

The manual has a section on optimising order by and that may help. So may MySQL 5.0.

For 3.n and 4.n you'd ideally want the index needed for the order by to also be the index best suited to handle the where clause, because you only get one index per table alias. Exploring the use of a self join or different variations in index choice can be useful.

Sometimes you might have a where a=something and order by b situation which can be replaced by where a=something order by a,b to get the same result and make it more obvious that an index on a,b is the right choice.