MySQL multi join query issue

Databases
1

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)
2

try it this way –


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
     , ( SELECT GROUP_CONCAT(pc_id) 
           FROM pg_categories 
          WHERE pg_id = pg.pg_id ) AS cat
     , ( SELECT COUNT(p_id) 
           FROM photos_galleries 
          WHERE pg_id = pg.pg_id ) AS number 
     , ( SELECT GROUP_CONCAT(p_town_id) 
           FROM pg_towns 
          WHERE pg_id = pg.pg_id ) AS towns
  FROM p_galleries AS pg
INNER
  JOIN pg_photographer AS p
    ON pg.pg_id = p.pg_id 
 WHERE pg.pg_id = $(a specific gallery id)