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