tactics to improve ORDER BY and LIMIT (different question)
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
Subject
Views
Written By
Posted
tactics to improve ORDER BY and LIMIT (different question)
3872
March 03, 2009 02:55PM
1963
March 03, 2009 08:26PM
1883
March 13, 2009 09:22AM
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.