MySQL Indices and Order By Clause

Question

Say I have a simple query like this:

SELECT * FROM topics ORDER BY last_post_id DESC

In accordance to MySQL's Order-By Optimization Docs, an index on last_post_id should be utilized. But EXPLAINing the query tells the contrary:

id  select_type table type possible_keys key key_len ref rows Extra
1   SIMPLE topic_info ALL NULL NULL NULL NULL 13 Using filesort

Why is my index not being utilized?

Answer 1

Do you really only have 13 rows? The database may be deciding that simply sorting them is quicker than going via the index.

Answer 2

Try selecting the specific columns in order as they are in the table. MySQL indexes don't hold when the order is changed.

Answer 3

A basic principle of index optimization is to use representative data for testing. A few rows in a table has no predictive value about how either the index or the optimizer will work in real life.

If you really have only a few records, then the indexing will provide no effective benefit.

Answer 4

EXPLAIN only tells you about the selection process, in which case it's expected that the query would have to examine all 13 rows to see if they meet the WHERE clause (which you don't have, so it's useless information!). It only reports the indexes and keys used for this purpose (evaluating WHERE, JOIN, HAVING). So, regardless of whether the query uses an index to sort or not, EXPLAIN won't tell you about it, so don't get caught up in its results.

Yes, the query uses the index to sort quickly. I've noticed the same results from EXPLAIN (aka reporting all rows despite being sorted by an index and having a limit) and I doubt it's a result of the small number of rows in your table, but rather a limitation of the power of EXPLAIN.