ORDER BY uses all rows

I want to show the 10 latest forum topics and I do it by ordering the date ASC. I put index on date, however, it still gets all rows to check (I use EXPLAIN to see that).

What is the p开发者_高级运维roblem or you can't see it without seeing my table?

Thank you.

depending on the type of index, ordering by date will need a full scan. I think you can't do much about that with mysql.

nevertheless, one solution is to "cut" the search using a WHERE clause. eg

WHERE date > 10 days ago

the ordering will be not be done on the full scan but on what is left after the WHERE clause.

Weird as it may seem, and depending on your table, you may be able to optimize your query with ... 2 queries. eg :

SELECT max(primary key) from topics => $max

SELECT topic from topics where primary key >= $max - 10

these 2 request will be faster than a full scan if your table has many lines and will certainly give the same result if your primary key is auto-increment.

I hope this will help you

Jerome Wagner

What you are describing should work, but is really easy to break http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html gives a list of reasons that it can break. Without seeing your SQL I don't know which of these you are doing. However here is an example of how to make this work:

SELECT topic
FROM forum_posts
ORDER BY some_date DESC
LIMIT 10

And if you have a more complex query that breaks this, you can join to this query to filter results and save work. For instance.

SELECT f.topic
  , o.some_information
  , some_date
FROM (
    SELECT id, topic, some_date
    FROM forum_posts
    ORDER BY some_date DESC
    LIMIT 10
  ) f
  JOIN other_table o
    ON o.forum_id = f.id
ORDER BY some_date DESC