Recent mySQL records

[James_43]

Hi all,

Say I have a table called "posts" with thousands of records. For each user, I want to display the top 10 most recent posts.

I would normally use:

select * from posts order by created_date desc limit 10

However, doesn't this select ALL records and then removes all but the top 10. For a huge database, this wouldn't be efficient.

Can anyone suggest an alternative?

Cheers!

[rproffitt]

I see this kicked around a lot such as this prior.

To see which is the most efficient you would have to benchmark and I didn't repeat what was covered in the prior discussions. However I always ask if select * is needed. Did you really need all that?

[ryantroop]

To answer your question - yes, you would limit to only the last 10 posts with your query.

You query would look something like this:

select
  U.UserKey,
  M.MessageKey
from
  User U
    LEFT OUTER JOIN Message M ON M.UserKey = U.UserKey
 where
   M.MessageKey IN (select M2.MessageKey from Message M2 where M2.UserKey = U.UserKey order by M2.PostDate DESC LIMIT 10)
 order by
   U.UserKey (or whatever.. date?)

Of course, your mileage may vary... but this should hopefully get you started.

For speed, you may want to use an inner join instead of "IN", but then youre gonna have some struggles dealing with the LIMIT and the order.. truth be told, Im not sure how best to do it without an IN, but I've always been told that an IN can always be replaced by a JOIN of some sort.