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