MySQL Sub-query.. Doesn't provide proper information after 3 entries into table.

Question

After I get 3 rows in my forum_threads table this no longer does it's job. Which is to organize a list of active forum threads and put the most recently responded-to thread at the top of the list, followed by second most recent posted-to thread, followed by third, fourth, etc.

Like I said, the query works wonders up until there is a fourth row added to forum_threads.

SELECT 
  forum_threads.*, 
  forum_posts.thread_id 
FROM 
  forum_threads 
  INNER JOIN (
      SELECT MAX(id) AS id, thread_id as thread_id 
        FROM forum_posts 
    GROUP BY thread_id 
    ORDER BY id DESC
  ) forum_posts ON forum_threads.id = forum_posts.thread_id

Answer 1

On first glance your query looks okay. Sometimes it helps to try the same thing differently to spot an error, so check what the following gives you:

SELECT 
  (SELECT MAX(id) FROM forum_posts WHERE thread_id = t.id) max_post_id, 
  t.id
FROM 
  forum_threads t
ORDER BY
  max_post_id DESC

Add a composite index to forum_posts over (thread_id, id).

Answer 2

You seem to be relying on MySQL to order the outer query in the same order as the inner query, without actually specifying what you want. Try moving the ORDER BY clause to the outer query:

SELECT 
  forum_threads.*, 
  forum_posts.thread_id 
FROM 
  forum_threads 
  INNER JOIN (
    SELECT MAX(id) AS id, thread_id as thread_id 
      FROM forum_posts 
    GROUP BY thread_id 
  ) forum_posts ON forum_threads.id = forum_posts.thread_id
ORDER BY forum_posts.id DESC