Optimizing Sorts

The last issue we want to cover in this case study is sorting. Sorting small result sets with filesorts is fast, but what if millions of rows match a query? For example, what if only sex is specified in the WHERE clause?

We can add special indexes for sorting these low-selectivity cases. For example, an index on (sex, rating) can be used for the following query:

mysql> SELECT <cols> FROM profiles WHERE sex='M' ORDER BY rating LIMIT 10;

This query has both ORDER BY and LIMIT clauses, and it would be very slow without the index.

Even with the index, the query can be slow if the user interface is paginated and someone requests a page that’s not near the beginning. This case creates a bad combination of ORDER BY and LIMIT with an offset: