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.

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.

• mysql

Share Improve this question Follow asked Feb 3 at 0:25 PhaelaxPhaelax 2,02222 gold badges1111 silver badges2525 bronze badges 2

• some sample edata and a dbfiddle would help to see whqt you are searching for – nbk Commented Feb 3 at 0:51
• 1 you can add some data to dbfiddle.uk/sZxzJ4Os there you also see how it should be done – nbk Commented Feb 3 at 1:07

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

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