MySQL: latest comments on each post

Question

Having the classic "posts table, and comments table with foreign key to posts table" scenario, what's the most efficient way to get the IDs of the last 20 posts ordered by the time of their last comment, and the actual comment itself?

Here is a query that works but can probably be done much more efficiently:

SELECT * FROM (
   SELECT * FROM comments ORDER BY time DESC
) AS foo GROUP BY post_id ORDER BY time DESC LIMIT 20 

A nested query with an ORDER BY is necessary to make sure that the latest comment gets selected into the post_id group.

Answer 1

As mentioned in the comments: practically the same question as Retrieveing the most recent records within a query.

See the greatest-n-per-group tag for more similar questions.