[eluser]krzycho[/eluser]
i've got 3 tables (this is simplified example):
photos:
id_photo int
comments:
id_comment int
id_photo int
comment text
votes:
id_vote int
id_photo int
vote int
now, i want to get all photos from photos table with comments and votes count for each table. something like:
id_photo | comments_count | votes_count
i figured out:
Code:
SELECT photos.*, count(votes.id_photo) AS votes_count
FROM
(SELECT COUNT(comments.id_photo) AS comments_count, photos.*
FROM photos
LEFT JOIN comments
ON photos.id_photo=comments.id_photo
GROUP BY photos.id_photo) AS photos
LEFT JOIN votes
ON votes.id_photo=photos.id_photo
GROUP BY photos.id_photo;
this returns correct values, but is quite slow.
in my case i got about 10000 records in photos table, 23000 records in comments table and about 28000 records in votes table. the query shown above (but on real tables which have more complex schema) takes about 2.5 sec, much too long, i thing. there are all needed indexes.
the question is: is there a simplest qnd quickest query to achieve what i need?