LifeinAsia - 3:53 pm on Oct 19, 2012 (gmt 0)

A few problems with that method...

You're making 3 calls to the posts table:
1) SELECT poster FROM posts (which has to scan the entire table and keep the results in memory)
2) SELECT MAX(temp2.posted) FROM posts
3) SELECT topic_id,posted,poster,message FROM posts temp1

I see #1 as a complete waste because it pulls everything (including duplicate poster values). Plus it doesn't do anything to match the actual poster with the posts identified in #2.

In fact, the "AND poster in (SELECT poster FROM posts)" clause is basically the same as saying "AND 1=1" in this case.

Try your query with and without "AND poster in (SELECT poster FROM posts)" and I suspect you'll get the same results (except that it should run faster without it).

Otherwise, the logic is basically the same as what I posted- not sure which is more efficient. (And depending on your DBMS, it may rewrite both into a more efficient 3rd version under the hood. :) )