HELP with SQL Query

[taterska]

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

[r937]

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?

[taterska]

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!

[r937]

I tried again but the spaces get removed once the post is submitted:-(

use [code]...[/code] tags

like this --

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

[taterska]

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?

[r937]

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