Find last row in table (MySQL)

More efficient than ORDER BY id DESC LIMIT 1

[surfgatinho]

I'm running the following query to get the last post in a forum. It's quite a big table, around 100,000 rows and it is constantly showing up in the slow query log:

SELECT post_id FROM posts WHERE forum_id = 1 ORDER BY post_id DESC LIMIT 1;

It seems like a real waste of effort to select all 100,000 rows and then sort just to get the last one.

Any better ideas?

Thanks,
Chris

[janharders]

does it have a combined index on forum_id and post_id?
if not, add that and it should be very fast.

if it does, use EXPLAIN ... to see wether the query planner uses the index.

[surfgatinho]

Thanks for that. I set up the combined index but it didn't seem to be used until I added USE INDEX(post_forum_idx)

It seems to be running the query faster now. Will keep an eye on the slow query log and see if it shows up again.