Slow MySQL Query When Run As A Procedure

Question

If I run this query directly in PHPMyAdmin, it returns 13420 rows in 0.2091 second, but if I run the exact same query as a stored procedure, it returns the same amount of row but it takes forever and sometimes the SQL server returns an out of memory exception.

I'm at a total loss - any advice would be welcome, because I can't work out why this slows everything down?!

    SELECT
    el.UID as LUID, 
    se.UID as DUID, 
    el.event_title, 
    el.event_synopsis, 
    se.behind_the_scenes, 
    se.sub_event_title, 
    se.event_eventDateAndTime, 
    se.event_eventDateAndTimeEnd, 
    el.event_confirmed, 
    el.event_active, 
    (
        SELECT GROUP_CONCAT(sp2.color SEPARATOR ',')
        FROM setup__spaces sp2
        LEFT JOIN events__assigned_spaces eas2 ON ( eas2.space_id = sp2.UID )
        LEFT JOIN events__events_list el2 ON (el2.UID = eas2.event_id)
        WHERE el2.UID = el.UID
    ) as spaceColors, 
    (
        SELECT GROUP_CONCAT(sp2.name SEPARATOR ', ')
        FROM setup__spaces sp2
        LEFT JOIN events__assigned_spaces eas2 ON ( eas2.space_id = sp2.UID )
        LEFT JOIN events__events_list el2 ON (el2.UID = eas2.event_id)
        WHERE el2.UID = el.UID
    ) as spaceNames, 
    (
        SELECT GROUP_CONCAT(sp2.UID SEPARATOR ',')
        FROM setup__spaces sp2
        LEFT JOIN events__assigned_spaces eas2 ON ( eas2.space_id = sp2.UID )
        LEFT JOIN events__events_list el2 ON (el2.UID = eas2.event_id)
        WHERE el2.UID = el.UID
    ) as spaceIds, 
    (
        SELECT GROUP_CONCAT(t.UID SEPARATOR ',')
        FROM setup__tags t
        LEFT JOIN events__assigned_tags eat ON ( eat.tag_id = t.UID )
        LEFT JOIN events__events_list el2 ON (el2.UID = eat.event_id)
        WHERE el2.UID = el.UID
    ) as tagIds, 
    (
        SELECT GROUP_CONCAT(t.tag_name SEPARATOR ', ')
        FROM setup__tags t
        LEFT JOIN events__assigned_tags eat ON ( eat.tag_id = t.UID )
        LEFT JOIN events__events_list el2 ON (el2.UID = eat.event_id)
        WHERE el2.UID = el.UID
    ) as tagNames 
FROM events__events_list el 
INNER JOIN events__sub_events se ON (el.UID = se.event_masterEvent) 
WHERE ((el.event_active='1') OR (el.event_active='0' AND el.event_confirmed = '1'))
AND el.company_uid = sp_company_uid

Remove correlated subqueries, make one subquery in FROM as one more data source. — Akina, Sep 05 '22 at 17:47
Forgive me: how would I do that? I'm at the edge of my MySQL abilities! — Blind Trevor, Sep 05 '22 at 18:02
Thank you @Akina - that solved my issue. I'll answer this question to help people stumbling on this in the future! — Blind Trevor, Sep 05 '22 at 20:04
"the SQL server" -- I assume you are referring to "MySQL", not "SqlServer"? — Rick James, Sep 07 '22 at 14:57
"as a stored procedure" -- With arguments? That are filled into the query? Which columns? — Rick James, Sep 11 '22 at 17:03

Blind Trevor · Accepted Answer · 2022-09-06T08:47:50.687

With thanks to @Akina for pointing me in the right direction, I found help from the following places:

stackoverflow.com - Selecting multiple columns/fields in MySQL subquery geeksengine.com - How to use subquery in JOIN operation in MySQL

Here's the revised code that's now lightning fast!

SELECT
    el.UID as LUID, 
    se.UID as DUID, 
    el.event_title, 
    el.event_synopsis, 
    se.behind_the_scenes, 
    se.sub_event_title, 
    se.event_eventDateAndTime, 
    se.event_eventDateAndTimeEnd, 
    el.event_confirmed, 
    el.event_active, 
    tags.names as tagNames,
    tags.ids as tagIds,
    spaces.names as spaceNames,
    spaces.colors as spaceColors,
    spaces.ids as spaceIds
FROM events__events_list el 
INNER JOIN events__sub_events se ON (el.UID = se.event_masterEvent)
LEFT JOIN (
    SELECT 
        el3.UID as el2uid, 
        GROUP_CONCAT(s.name SEPARATOR ', ') as names, 
        GROUP_CONCAT(s.color SEPARATOR ',') as colors, 
        GROUP_CONCAT(s.UID SEPARATOR ',') as ids
    FROM setup__spaces as s
    LEFT JOIN events__assigned_spaces eas ON ( eas.space_id = s.UID )
    LEFT JOIN events__events_list el3 ON ( el3.UID = eas.event_id )
    GROUP BY el3.UID
) as spaces on spaces.el2uid = el.UID
LEFT JOIN (
    SELECT 
        el2.UID as el2uid, 
        GROUP_CONCAT(t.tag_name SEPARATOR ', ') as names, 
        GROUP_CONCAT(t.UID SEPARATOR ',') as ids
    FROM setup__tags as t
    LEFT JOIN events__assigned_tags eat ON ( eat.tag_id = t.UID )
    LEFT JOIN events__events_list el2 ON ( el2.UID = eat.event_id )
    GROUP BY el2.UID
) as tags on tags.el2uid = el.UID
WHERE ((el.event_active='1') OR (el.event_active='0' AND el.event_confirmed = '1'))
AND el.company_uid = sp_company_uid

Ah, thanks... they were in there, but I took them out when trying to fault find. I'll put them back in! — Blind Trevor, Sep 08 '22 at 15:02

Slow MySQL Query When Run As A Procedure

1 Answers1