I have two tables making up a simple forum design; thread (which is just the subject) and then post with a zero to many relationship based on thread.id and post.threadId
What I'm trying to do is order threads based on the most recent post.postDate associated with it.
thread
--------------
id (PRI, int)
title (varchar)
post
--------------
id (PRI, int)
postDate (datetime)
threadId (int)
What I have so far technically works, I think, it takes a very long time to run.
SELECT thread.id, title, (SELECT postDate FROM post WHERE post.threadId = thread.id ORDER BY postDate DESC LIMIT 1) as lastUpdate
FROM thread ORDER BY lastUpdate DESC LIMIT 10 OFFSET 30;
There has to be a better way to write this as this is extremely slow. The thread table has over 10k rows and post contains over 100k, and this is only a small subset of the full data which I expect to be about 10x larger at least. The ORDER BY in the main query is what's slowing it down. Drop that and LIMIT and it'll return all 10k rows with the most recent post date in about 1-2 seconds. So if it can sort all those posts to retrieve the most recent on for each thread then I don't understand why the final result can't seem to sort by lastUpdate.
One last thing as well, I'm testing my query from phpmyadmin and after I get a result it states that there is no unique column, but the result includes thread.id which is a primary key.
It is slower with the ORDER BY because it has to read all the records in order to sort, and then LIMIT the output.
Does post.thread_id has index?
Consider using a JOIN instead of inner SELECT as it should be more optimized.
SELECT thread.id, title, p.lastUpdate
FROM thread
LEFT JOIN (
SELECT threadId, MAX(postDate) AS lastUpdate
FROM post
GROUP BY threadId
) AS p ON thread.id = p.threadId
ORDER BY p.lastUpdate DESC
LIMIT 10