SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    HELP with SQL Query

    Hi,

    I've been trying to solve this too long time now, please if anyone have any ideas I would be most grateful. I have to optimize the following query:

    SELECT SQL_CALC_FOUND_ROWS

    p.id,p.api_id, p.description, p.member_date, p.public_date, p.published, p.featured,

    MIN(d.date) AS start_date, MAX(d.date) AS end_date,

    ROUND(( MAX(d.date) - MIN(d.date)) / 86400) + 1 AS duration,

    h.name, h.id AS headline_id, h.seo_name,

    CONCAT(p.id, "-", h.seo_name) AS url, s.support, s.support_ids,

    (SELECT CONCAT_WS("|",COUNT(b.id),CONCAT_WS("|",SUM(b.quantity),SUM(b.total)))

    FROM booking b

    LEFT JOIN performance_ticket t ON (t.id = b.ticket)

    LEFT JOIN performance_house h ON (h.id = t.house)

    LEFT JOIN performance_date d ON (d.id = h.date)

    WHERE b.date_cancelled = 0

    AND d.performance = p.id

    GROUP BY d.performance

    ) AS bookingsANDticketsANDtotal,

    IF (t.remaining IS NOT NULL, IF (t.remaining > 0, t.remaining, 0), IF (t.available, t.available, 0)) AS remaining

    FROM performance p

    LEFT JOIN performance_date d ON (d.performance = p.id)

    LEFT JOIN (

    SELECT a.performance, GROUP_CONCAT( t.name ORDER BY orderby SEPARATOR ", " ) AS support,

    GROUP_CONCAT(t.id) AS support_ids

    FROM performance_act a

    LEFT JOIN artist t ON (t.id = a.artist)

    WHERE a.headline = 0

    GROUP BY a.performance )

    AS s ON (s.performance = p.id)



    LEFT JOIN ( SELECT d.performance, SUM(t.available) AS available, SUM(IF (b.quantity IS NOT NULL, GREATEST(t.available - b.quantity, 0), t.available)) AS remaining FROM performance_ticket t LEFT JOIN performance_house h ON (h.id = t.house) LEFT JOIN performance_date d ON (d.id = h.date) LEFT JOIN ( SELECT ticket, SUM(quantity) AS quantity FROM booking WHERE NOT date_cancelled GROUP BY ticket ) AS b ON (b.ticket = t.id) GROUP BY d.performance ) AS t ON (t.performance = p.id),

    artist h, performance_act a

    WHERE p.removed = 0

    AND a.performance = p.id

    AND a.artist = h.id

    AND a.headline = 1

    GROUP BY p.id

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'm sorry, i can't read that

    would you kindly format it properly, so that the levels of subqueries are indented

    and when you say "i have to optimize" ... could you please explain what's wrong with it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT SQL_CALC_FOUND_ROWS

    p.id,p.api_id, p.description, p.member_date, p.public_date, p.published, p.featured,

    MIN(d.date) AS start_date, MAX(d.date) AS end_date,

    ROUND(( MAX(d.date) - MIN(d.date)) / 86400) + 1 AS duration,

    h.name, h.id AS headline_id, h.seo_name,

    CONCAT(p.id, "-", h.seo_name) AS url, s.support, s.support_ids,

    (SELECT CONCAT_WS("|",COUNT(b.id),CONCAT_WS("|",SUM(b.quantity),SUM(b.total)))

    FROM booking b

    LEFT JOIN performance_ticket t ON (t.id = b.ticket)

    LEFT JOIN performance_house h ON (h.id = t.house)

    LEFT JOIN performance_date d ON (d.id = h.date)

    WHERE b.date_cancelled = 0

    AND d.performance = p.id

    GROUP BY d.performance

    ) AS bookingsANDticketsANDtotal,

    IF (t.remaining IS NOT NULL, IF (t.remaining > 0, t.remaining, 0), IF (t.available, t.available, 0)) AS remaining

    FROM performance p

    LEFT JOIN performance_date d ON (d.performance = p.id)

    LEFT JOIN (

    SELECT a.performance, GROUP_CONCAT( t.name ORDER BY orderby SEPARATOR ", " ) AS support,

    GROUP_CONCAT(t.id) AS support_ids

    FROM performance_act a

    LEFT JOIN artist t ON (t.id = a.artist)

    WHERE a.headline = 0

    GROUP BY a.performance )

    AS s ON (s.performance = p.id)
    LEFT JOIN
    ( SELECT d.performance, SUM(t.available) AS available, SUM(IF (b.quantity IS NOT NULL, GREATEST(t.available - b.quantity, 0), t.available))
    AS remaining
    FROM performance_ticket t
    LEFT JOIN performance_house h ON (h.id = t.house)
    LEFT JOIN performance_date d ON (d.id = h.date)
    LEFT JOIN
    ( SELECT ticket, SUM(quantity) AS quantity FROM booking WHERE NOT date_cancelled GROUP BY ticket ) AS b ON (b.ticket = t.id) GROUP BY d.performance )
    AS t ON (t.performance = p.id),

    artist h, performance_act a

    WHERE p.removed = 0

    AND a.performance = p.id

    AND a.artist = h.id

    AND a.headline = 1

    GROUP BY p.id


    Sorry, but it was supposed to be formatted. I tried again but the spaces get removed once the post is submitted:-(

    The query is running slow and the idea is to get rid of the subqueries somehow at least from the joints. Thanks for the help!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by taterska View Post
    I tried again but the spaces get removed once the post is submitted:-(
    use [code]...[/code] tags

    like this --
    Code:
    SELECT SQL_CALC_FOUND_ROWS 
           p.id
         , p.api_id
         , p.description
         , p.member_date
         , p.public_date
         , p.published
         , p.featured
         , MIN(d.date) AS start_date
         , MAX(d.date) AS end_date
         , ROUND(( MAX(d.date) - MIN(d.date)) / 86400) + 1 AS duration
         , h.name
         , h.id AS headline_id
         , h.seo_name
         , CONCAT(p.id,"-",h.seo_name) AS url
         , s.support
         , s.support_ids
         , ( SELECT CONCAT_WS("|"
                            , COUNT(b.id)
                            , CONCAT_WS("|",SUM(b.quantity),SUM(b.total))
                             )  
               FROM booking b
             LEFT 
               JOIN performance_ticket t 
                 ON t.id = b.ticket
             LEFT 
               JOIN performance_house h 
                 ON h.id = t.house
             LEFT 
               JOIN performance_date d 
                 ON d.id = h.date
              WHERE b.date_cancelled = 0 
                AND d.performance = p.id
             GROUP 
                 BY d.performance ) AS bookingsANDticketsANDtotal
         , IF ( t.remaining IS NOT NULL
              , IF ( t.remaining > 0
                   , t.remaining
                   , 0 )
              , IF ( t.available
                   , t.available
                   , 0 ) ) AS remaining
      FROM performance p
    LEFT 
      JOIN performance_date d 
        ON d.performance = p.id
    LEFT 
      JOIN ( SELECT a.performance
                  , GROUP_CONCAT( t.name ORDER BY orderby 
                                  SEPARATOR ", " ) AS support
                  , GROUP_CONCAT(t.id) AS support_ids
               FROM performance_act a
             LEFT 
               JOIN artist t 
                 ON t.id = a.artist
              WHERE a.headline = 0 
             GROUP 
                 BY a.performance ) AS s 
        ON s.performance = p.id                                    
    LEFT 
      JOIN ( SELECT d.performance
                  , SUM(t.available) AS available
                  , SUM(IF ( b.quantity IS NOT NULL
                           , GREATEST(t.available - b.quantity,  0)
                           ,  t.available ) ) AS remaining
               FROM performance_ticket t
             LEFT 
               JOIN performance_house h 
                 ON h.id = t.house
             LEFT 
               JOIN performance_date d 
                 ON d.id = h.date
             LEFT 
               JOIN ( SELECT ticket
                           , SUM(quantity) AS quantity 
                        FROM booking 
                       WHERE NOT date_cancelled 
                      GROUP 
                          BY ticket ) AS b 
                 ON b.ticket = t.id 
             GROUP 
                 BY d.performance ) AS t 
        ON t.performance = p.id
         , artist h
         , performance_act a
     WHERE  p.removed = 0 
       AND a.performance = p.id
       AND a.artist = h.id
       AND a.headline = 1GROUP 
        BY p.id
    the stuff in red should be re-written to use INNER JOIN

    having said that, i don't think i can help you, this query is ~way~ too complex
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Oct 2009
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks,

    I had a go with views, basically I put most of the subqueries from the JOINs in views and managed to speed up a little. What are the pros and cons of views?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    pros -- make writing queries easier, can be used to grant controlled access

    cons -- another level of meta-data to maintain, might not be optimized for certain queries
    rudy.ca | @rudydotca
    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
  •