How expensive is this query?
Lets say for example if I don't have a field called total_comments in my blog table. All I want to know is the blog has comments or not.
SELECT EXISTS (SELECT true FROM comments WHERE blog_id = b.id) as has_comments FROM blogs b
How else could this be achieved?
UPDATE: Ah...I've reread your original query. When I first read it, I thought that you were only retrieving this for a single blog, but now I see that you're retrieving for every blog record.
This might be inefficient, though I'm not sure. I suppose it depends on how good MySQL is at dealing with that subquery.
Below is probably the most straightforward approach:
SELECT
blogs.blog_id,
COUNT(comments.blog_id)
FROM blogs
LEFT OUTER JOIN comments on
comments.blog_id = blogs.blog_id
GROUP BY
blogs.blog_id
Of course, this gives you a count, which is more information that was asked for, but this will probably be pretty efficient if you have an index on blog_id.
Another alternative is to union together a result of the blogs that have comments with another result of the blogs that don't have comments.
SELECT blog_id, true
FROM blogs
WHERE
EXISTS (
SELECT true
FROM comments
WHERE
comments.blog_id = blogs.blog_id
)
UNION ALL
SELECT blog_id, false
FROM blogs
WHERE
NOT EXISTS (
SELECT true
FROM comments
WHERE
comments.blog_id = blogs.blog_id
)
