mysql - Join vs subquery to count nested objects -

let's model contains 2 tables: persons , addresses. 1 person can have o, 1 or more addresses. i'm trying execute query lists persons , includes number of addresses have respectively. here 2 queries have achieve that:

select   persons.*,   count(addresses.id) number_of_addresses `persons`   left join addresses on persons.id = addresses.person_id group persons.id 

and

select   persons.*,   (select count(*)    addresses    addresses.person_id = persons.id) number_of_addresses `persons` 

and wondering if 1 better other in term of performance.

the way determine performance characteristics run queries , see better.

if have no indexes, first better. if have index on addresses(person_id), second better.

the reason little complicated. basic reason group by (in mysql) uses sort. and, sorts o(n * log(n)) in complexity. so, time sort grows faster data (not faster, bit fast). consequence bunch of aggregations each person faster 1 aggregation person on data.

that conceptual. in fact, mysql use index correlated subquery, faster overall group by, not make use of index.