What is the difference between subquery and a joined subquery?

Question

What is the difference between these two mysql queries

select t.id,
(select count(c.id) from comment c where c.topic_id = t.id) as comments_count
from topic;

AND

select t.id,comments.count from topic
left join
(
   select count(c.id) count,c.topic_id from comment c group by topic_id
) as comments on t.id = comments.topic_id

I know theres not much information. Just wanted to know when to use a subquery and joined subquery and whats the difference between them.

Thanks

Answer 1

This is a good question, but I would also add a third option (the more standard way of doing this):

select t.id, count(c.topic_id) as count
from topic left join
     comment c 
     on t.id = c.topic_id
group by t.id;

The first way is often the most efficient in MySQL. MySQL can take advantage of an index on comment(topic_id) to generate the count. This may be true in other databases as well, but it is particularly noticeable in MySQL which does not use indexes for group by in practice.

The second query does the aggregation and then a join. The subquery is materialized, adding additional overhead, and then the join cannot use an index on comment. It could possibly use an index on topic, but the left join may make that option less likely. (You would need to check the execution plan in your environment.)

The third option would be equivalent to the first in many databases, but not in MySQL. It does the join to comment (taking advantage of an index on comment(topic_id), if available). However, it then incurs the overhead of a file sort for the final aggregation.

Reluctantly, I must admit that the first choice is often the best in terms of performance in MySQL, particularly if the right indexes are available. Without indexes, any of the three might be the best choice. For instance, without indexes, the second is the best if comments is empty or has very few topics.