Find last row in table (MySQL)

Forum Moderators: open

Message Too Old, No Replies

Find last row in table (MySQL)

More efficient than ORDER BY id DESC LIMIT 1

surfgatinho

9:07 am on Jun 28, 2009 (gmt 0)

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

9:16 am on Jun 28, 2009 (gmt 0)

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

11:51 am on Jun 28, 2009 (gmt 0)

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.