title: MySQL: latest comments on each post
body: <p>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?</p>
  
  <p>Here is a query that works but can probably be done much more efficiently:</p>
  
  <pre><code>SELECT * FROM (
     SELECT * FROM comments ORDER BY time DESC
  ) AS foo GROUP BY post_id ORDER BY time DESC LIMIT 20 
  </code></pre>
  
  <p>A nested query with an <code>ORDER BY</code> is necessary to make sure that the latest comment gets selected into the <code>post_id</code> group.</p>