MySQL query: blog entry has comments or not

Question

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?

• palacsint: It depends on how often you execute the query.
• Lars-Erik: also depends (a lot) whether you have an index on the blog_id field of the comments table.

Answer 1

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
 )

• Contra: Thanks it going to be interesting to see the performance between count+group vs exists. And in your second solution you put subquery in the where where mine is in the select. Will have to do some benchmarks here. I am going to mark this as the correct solution. But will update this question with the benchmarks later this week.
• Dr. Wily's Apprentice: @Contra - I'm not sure about MySQL, but I believe that in older versions of SQL Server, you would get better performance by putting an "exists" condition in the where clause rather than as a column in the result. I believe that modern versions of SQL Server are intelligent enough to optimize the query either way, though.