mysql - Nested query performance -

i have 2 queries below. first 1 has nested select. second 1 makes use of group clause.

select   posts.*,   (select count(*) comments comments.post_id = posts.id , comments.is_approved = 1) comments_count   posts   select   posts.*,   count(comments.id) comments_count   posts    left join comments on      comments.post_id = posts.id  group   posts.* 

from understanding first query worse because has select each record in posts second query not.

is true or false?

as performance questions, should test performance on system data.

however, expect first perform better, right indexes. right index for:

select p.*,        (select count(*)         comments c         c.post_id = p.id , c.is_approved = 1        ) comments_count posts p 

is comments(post_id, is_approved).

mysql implements group by doing file sort. version saves file sort on data. guess faster second method.

as note: group posts.* not valid syntax. assume intended illustration purposes only.