Results 1 to 2 of 2
Thread: MySQL multi join query issue
Aug 23, 2013, 06:00 #1
- Join Date
- Aug 2013
- 0 Post(s)
- 0 Thread(s)
MySQL multi join query issue
So I ran into an interesting problem yesterday with Rackspace Cloud. They changed there mySQL settings placing a limit on the number of rows a join can create, the number is huge so it shouldn't be an issue. That being said this one query is a major issue. From what they said I am creating more than a quadrillion rows with this query for a final output of only 307 rows. Clearly this is a problem. So I am here to ask does anyone see where I am going so wrong. Bear in mind this query gives me everything I need and completes in way under a second.
Any help or ideas is really appreciated. I guess a hint would be if I remove the first join it works fine under the limit.
SELECT pg.pg_id , pg.pg_title , pg.pg_description , pg.pg_main_photo , pg.pg_purchase_link , UNIX_TIMESTAMP(pg.gallery_date) AS gdate , pg.pg_purchase_link , p.photog_id , p.pg_id , pg.gallery_type , cats.cat , photos.number , town.towns FROM p_galleries AS pg JOIN pg_photographer AS p ON pg.pg_id = p.pg_id LEFT OUTER JOIN (SELECT GROUP_CONCAT(pc_id) AS cat, pg_id FROM pg_categories GROUP BY pg_id) AS cats ON pg.pg_id = cats.pg_id LEFT OUTER JOIN (SELECT GROUP_CONCAT(p_town_id) AS towns, pg_id FROM pg_towns GROUP BY pg_id) AS town ON pg.pg_id = town.pg_id LEFT OUTER JOIN (SELECT COUNT(p_id) AS number, pg_id FROM photos_galleries GROUP BY pg_id) AS photos ON photos.pg_id = pg.pg_id WHERE pg.pg_id = $(a specific gallery id)