SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Aug 2013
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    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.

    Code:
    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    try it this way --
    Code:
    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)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •