2
\$\begingroup\$

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?

\$\endgroup\$
2
  • \$\begingroup\$ It depends on how often you execute the query. \$\endgroup\$ – palacsint Jan 24 '12 at 11:20
  • \$\begingroup\$ also depends (a lot) whether you have an index on the blog_id field of the comments table. \$\endgroup\$ – Lars-Erik Jan 24 '12 at 15:20
1
\$\begingroup\$

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
    )
\$\endgroup\$
2
  • \$\begingroup\$ 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. \$\endgroup\$ – Contra Jan 25 '12 at 7:08
  • \$\begingroup\$ @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. \$\endgroup\$ – Dr. Wily's Apprentice Jan 25 '12 at 14:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.