Quote:
|
Originally Posted by welshbyte
Are t1.column1 and t2.column2 both made to be distinct or would there be repetitions in column2?
|
Not sure what database you are using, I live in Postgres land, so some things will probably be different. With Postgres you can do a
Code:
SELECT DISTINCT ON(column1[, column2...]) * FROM whatever
The scary part here is that unless you are careful with your ORDER BY's, what you get for the columns that aren't DISTINCTed can be undefined and different from query to query.
DISTINCT is almost never the Right Way to do something.
Your SQL looks expensive, in similar situations I would tend to have something like:
Code:
SELECT sp.post_id, t.whatever, f.whatever, u.whatever
FROM (
SELECT MAX(p.post_id)
FROM posts AS p
JOIN topics USING (p.topic_id)
GROUP BY p.topic_id
ORDER BY MAX(p.post_id) DESC LIMIT 5
) AS sp
JOIN topics t USING(topic_id)
JOIN forums f USING (forum_id)
JOIN users u USING (user_id)
Of course I am assuming that your post_id's are incrementing. The execute trees for both of these may very well be similar, but this way I feel I can have a better guess at what the optimizer will do. There seems to be little reason to make those JOINs 'LEFT', but then that three join combination (post -> user; post -> thread -> forum) looks common enough that you probably have it wrapped in a VIEW.
If your database doesn't support that construct (whatever they are called - dynamic tables? I think), then you probably want two queries: get the post_id's with the first and stick them in an IN() in the second. Unless you are running Oracle, the cost of the extra query is completely negligible (and even with Oracle it probably won't be noticeable), just prepare both queries in advance.
Of course if performance does become an issue (ie huge forum and this is a very frequent query), you can think about denormalizing and putting the forum_id into the posts table; but of course lots of people have lots of strong (and divergent) opinions about the merits of doing that.
Anyway, I hope any of this helped at all, I realize it's not exactly specific to your situation.