Often I have to return the count of of some row in a query that fetch other non related rows.
For example A table User a table Review and a Table picture
User: id nickname Review: id to_user_id from_user_id rating Picture: id: user_id url
Let's say I want in one single query retrieve the nickname of a 'given' userId all its pictures url as well as the count of how many people reviewed that user.
The first and easy way I think when doing this query would this:
SELECT u.nickname (SELECT count(*) FROM review WHERE to_user_id = u.id) as reviewCount, p.url FROM user LEFT JOIN picture ON p.user_id = u.id WHERE u.id = 1
The other way of doing this is without that subselect and by joining the review table on the right user_id
SELECT u.nickname, r.reviewCount, p.url FROM user u LEFT JOIN ( SELECT to_user_id, count(*) reviewCount FROM review GROUP BY to_user_id ) r ON r.to_user_id = u.id LEFT JOIN picture ON p.user_id = u.id WHERE u.id = 1;
I'm not very an expert with db query performance and tuning. Could someone explain me if a solution is better than the other? (Or if there is an other better solution) ?
EDIT: Sorry forgot to mention. I'm working with the latest MySQL