MySQL Forums
Forum List  »  Performance

tactics to improve ORDER BY and LIMIT (different question)
Posted by: Denis Haskin
Date: March 03, 2009 02:55PM

Slight different question than the previous one about ORDER BY/LIMIT.

We have several places in our app where we show the most recent 'n' items, usually with some other criteria.

So, for example a query like:

select p.* from posts p where status='approved' order by created_at DESC limit 60;

This does not have a great query plan:

mysql> explain select p.* from posts p where status='approved' order by created_at desc limit 60;
+----+-------------+-------+------+-----------------------+-----------------------+---------+-------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------------+-----------------------+---------+-------+--------+-----------------------------+
| 1 | SIMPLE | p | ref | index_posts_on_status | index_posts_on_status | 768 | const | 216833 | Using where; Using filesort |
+----+-------------+-------+------+-----------------------+-----------------------+---------+-------+--------+-----------------------------+
1 row in set (0.00 sec)

(there is an index on CREATED_AT, and I also tried creating indexes on (STATUS, CREATED_AT) and (CREATED_AT, STATUS)--neither helped).

The cardinality on STATUS is very low--almost all posts have STATUS='approved'.

Is there a way to improve this query (particularly to avoid the filesort)? Or do I have to investigate some non-database way of trying to avoid this query?

Thanks...

dwh

Options: ReplyQuote


Subject
Views
Written By
Posted
tactics to improve ORDER BY and LIMIT (different question)
3872
March 03, 2009 02:55PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.