Obtaining employees and their roles, and vice versa

Question

I am using the following query to obtain a employee with his information, and get all his related roles. The query underneath works fine when we check only 1 location taking only about 0.002 seconds to process, but once we go over ~800 locations it sometimes takes up to 8 full seconds to process the request.

In the actual query I also obtain the roles with all employees bound to them (inverted version of the one below) as a sub query.

Would there be a way to improve this query on higher amounts of locations? Keys and indexes are already set properly and are also being used correctly. which originally gave a speed win of 10 seconds (without them it took 18 seconds).

SELECT
ifNULL((
    GROUP_CONCAT(
    '<?xml version=\'1.0\' encoding=\'utf-8\'?>',
    '<Employee xmlns:xsi=\'http://www.w3.org/2001/XMLSchema-instance\' xmlns:xsd=\'http://www.w3.org/2001/XMLSchema\'>',
        '<id><![CDATA[',loc.id,']]></id>',
        -- Ommited variable assignment
        ifnull((
            SELECT
                CONCAT(
                '<employees>',
                    GROUP_CONCAT(
                    '<Employee>',
                        '<id>',emp.id,'</id>',
                        '<name><![CDATA[',ifnull(emp.name,''),']]></name>',
                        ifnull((
                            SELECT
                                CONCAT(
                                '<photos>',
                                    GROUP_CONCAT(
                                    '<Photo>',
                                        '<id><![CDATA[',IFNULL(photos.id, '0'),']]></id>',
                                        '<name><![CDATA[',IFNULL(photos.name,''),']]></name>',
                                        '<orderValue><![CDATA[',IFNULL(photos.order_value,'0'),']]></orderValue>',
                                        '<state><![CDATA[', IFNULL(photos.state,'0'),']]></state>',
                                        '<originalImageWidth><![CDATA[', IFNULL(photos.original_image_width,'0'),']]></originalImageWidth>',
                                        '<originalImageHeigth><![CDATA[', IFNULL(photos.original_image_height,'0'),']]></originalImageHeigth>',
                                    '</Photo>'
                                    SEPARATOR ''
                                    )
                                ,'</photos>'
                                )
                            FROM
                                photos
                                INNER JOIN employee_photos
                                    ON photos.id = employee_photos.photo_id and photos.state = 1
                            WHERE 
                                employee_photos.employee_id = emp.id
                            ORDER BY
                                photos.order_value ASC
                            )
                            ,'<photos/>'
                        ),
                        '<isAction>',if(emro.id is null, 'false', 'true'),'</isAction>',
                        ( 
                            SELECT
                                CONCAT(
                                '<roles>',
                                    GROUP_CONCAT(
                                            '<int><![CDATA[',er.accommodation_id,']]></int>'
                                        SEPARATOR ''
                                    ),
                                '</roles>'
                                )
                            FROM
                                employee_roles as er
                            WHERE
                                er.employee_id = emp.id and er.available = 1
                        ),
                    ),
            '</Employee>'
            SEPARATOR ''
            ),
        '</employees>'
        FROM 
            employees AS emp
            left join employee_roles emro
                on emp.id = emro.employee_id and emro.action_id = @action_id
        WHERE
            emp.location_id = loc.id and emp.state = 1
    ),'<packages/>'),
    -- Ommited vairable assignment
'</Employee>'
) as result
FROM
locations AS loc
-- Inner joins not directly relevant to question
INNER JOIN cities
ON (loc.city_id=cities.id)
INNER JOIN provinces 
ON (loc.province_id=provinces.id)
INNER JOIN countries 
ON (loc.country_id=countries.id)
INNER JOIN regions
on (loc.region_id=regions.id)
WHERE
loc.state = 1 and loc.id = @locationid

Answer 1

Nevermind optimizing the speed of the thing, the most urgent fix is to be able to just read it!

Start with the easy stuff, the FROM clause:

FROM locations AS loc
-- Inner joins not directly relevant to question
INNER JOIN cities ON (loc.city_id=cities.id)
INNER JOIN provinces ON (loc.province_id=provinces.id)
INNER JOIN countries ON (loc.country_id=countries.id)
INNER JOIN regions ON (loc.region_id=regions.id)

Now you can see how many tables are involved, at a glance, without having to mentally sort out INNER JOIN's and ON chunks.

Be consistent with keyword casing. the last ON is spelled on, and the AND in your WHERE clause is spelled and - but when you glance at the script you think all keywords are in YELLCASE. Consistency helps readability there. Same goes for ifnull and ifNULL and is null, which should be IFNULL and ISNULL.

WHERE loc.state = 1 AND loc.id = @locationid

Now, that SELECT statement needs to be broken down. I'd suggest you take each subquery and turn them into distinct views that you can select from, to remove the clutter.

It seems your query is doing two very distinct things: one, it's selecting the data that you need to "serialize" into an XML format. Two, it's "serializing" data into an XML format... manually... by concatenating hard-coded strings.

I'd change the query to do only one of these things, and if possible, use another tool for turning it into XML.