LEFT JOIN on a big query

[mek2600]

I have the following query (trimmed down for the sake of clarity)

SELECT blogs.blog_id, count(comments.comment_id) num_comments
FROM blogs
LEFT JOIN comments ON blogs.blog_id = comments.blog_id
WHERE type = 'blog' GROUP BY date DESC, time DESC LIMIT 40

Here's my problem- when I do this it seems that it's matching all the blogs (close to 800) with all the comments (close to 1500). What I really need is for the comments to only match the first 40 blogs so that it's not matching for the nearly 800 blogs, then trimming to 40. When I started this site that wasnt a problem (and I didn't even notice the imperfect method), but now it's beginning to become very slow and I don't want to resort to 2 queries to accomplish this task. Any suggestions?

A hint is all I need. I like the challenge, but I just can't figure out where to begin. Thanks for the help.

[ergophobe]

Just to get this straight

only match the first 40 blogs

first 40 blogs based on what criteria? Are you looking for all comments from the 40 blogs that have most recently had comments?

So you want to
- get all the comments, arranged in descending order by date/time the comment was made
- figure out how far down that list you have to go until you have comments from 40 different blogs
- give a list of those 40 blogs and all their comments.

Is that the goal?

[mek2600]

aah, i did say that badly, but you're pretty close.

i have a page displaying the most recent 40 blogs by a number of people. what i need to do (in 1 query if possible- my goal is to learn more about SQL than to really amke it work- 2 queries is a cinch in this case) is grab the last 40 (based upon submit time), then merge those 40 with their comments in a LEFT JOIN or whatever.

right now, my LEFT JOIN statement is grapping ALL the blogs, matching them with the comments and then trimming it down to 40, which is a dog to do. trimming, then matching is what i need.