+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: mysql 3 table join (/showthread.php?tid=9981)
mysql 3 table join - El Forum - 07-15-2008
[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?
mysql 3 table join - El Forum - 07-15-2008
[eluser]Steve Grant[/eluser]
Should be. Using subqueries almost always slows the query down. Try to condense it down so the joins are all within the one query.
Something like
Code:
SELECT photos.*, COUNT(comments.id_photo) AS comments_count, COUNT(votes.id_photo) AS votes_count
FROM photos
LEFT JOIN comments ON photos.id_photo = comments.id_photo
LEFT JOIN votes ON votes.id_photo = photos.id_photo
GROUP BY photos.id_photo;I've not tested that query, so give it a go and see if you still get the right output.
mysql 3 table join - El Forum - 07-15-2008
[eluser]krzycho[/eluser]
yes, subqueries are slow.
steve, the query you posted returns wrong values for comments_count and vote_count- both are products of comments_count and vote_count.
mysql 3 table join - El Forum - 07-15-2008
[eluser]hvalente13[/eluser]
Hi,
It's just a suggestion:
I only make joins for small tables (ex: employees table and join to gender table, position table, etc...).
For other tables that have a lot of rows, I make separate queries (ex: zip codes)...
I've read this some place, that it gives more performance to database queries... I tested it and got some improvement.
Give it a try.