I'm trying to pass some joomla user data to a Datatable (jQuery plugin). There are 4 tables involved
joo_users (~10k rows),joo_user_groups(~1.7k rows),joo_user_usergroup_map (~56k rows),joo_user_profiles ( ~1398k rows)the query now:
SELECT SQL_CALC_FOUND_ROWS
a.id,
a.name,
MAX( IF(profiles.profile_key = 'profile.email', profiles.profile_value, NULL) ) AS email,
MAX( IF(profiles.profile_key = 'profile.codice_agente', profiles.profile_value, NULL) ) AS codice_agente,
MAX( IF(profiles.profile_key = 'profile.codice_agente_fisso', profiles.profile_value, NULL) ) AS codice_agente_fisso,
MAX( IF(profiles.profile_key = 'profile.codice_agente_VAR', profiles.profile_value, NULL) ) AS codice_agente_VAR,
MAX( IF(profiles.profile_key = 'profile.cellulare', profiles.profile_value, NULL) ) AS cellulare,
(SELECT
GROUP_CONCAT(DISTINCT b.title)
FROM
joo_user_profiles AS up
LEFT JOIN
joo_usergroups AS b ON REPLACE(up.profile_value, '"', '') = b.id
WHERE
up.profile_key LIKE 'profile.agenzia_%'
AND up.user_id = a.id
AND profile_value != '""') AS agenzia,
GROUP_CONCAT(DISTINCT REPLACE(IF(profiles.profile_key LIKE 'profile.canale_%' AND profiles.profile_value = '1', profiles.profile_key, NULL),'profile.canale_','') ) AS canale,
GROUP_CONCAT(DISTINCT IF(profiles.profile_key LIKE 'profile.area_%' AND profiles.profile_value != '""', profiles.profile_value, NULL)) AS area,
(SELECT
GROUP_CONCAT(DISTINCT b.title)
FROM
joo_user_profiles AS up
LEFT JOIN
joo_usergroups AS b ON REPLACE(up.profile_value, '"', '') = b.id
WHERE
up.profile_key LIKE 'profile.ruolo_%'
AND up.user_id = a.id
AND profile_value != '"0"') AS ruolo,
GROUP_CONCAT(IF(profiles.profile_key LIKE 'profile.status_%' AND profiles.profile_value != '""', profiles.profile_value, NULL)) AS status
FROM `joo_users` as a
LEFT JOIN joo_user_profiles AS profiles ON a.id = profiles.user_id
GROUP BY id
ORDER BY id
asc
LIMIT 0, 20
this beast takes 40sec on the production server and it's not really acceptable.
i know views don't bring any performance gain and i've indexed every column i can think of. Do you have any advice for me ?
I don't know why you need the calc_found_rows, but left it in there. As for the query, since all your group_concats were mostly against the profile table and optionally the user groups, I did a prequery (via tmpGrpCat alias) that just did the users with their corresponding group_concat and max() for elements such as email, condice..., cell, etc. I also included in this pre-query to exclude where the profile value was not "" as that would appear to be consistent across the board for your interested values.
I then did a simple left-join on the users table to get their id/name and the rest of the aggregates. This should simplify the engine to run through all the records ONCE for each of the profile_key LIKE (or equality) conditions once, group them by user's ID, then have it ready to go for the outermost query.
If this works, let us know performance improvement. If also can help others with performance techniques in future too.
SELECT SQL_CALC_FOUND_ROWS
a.id,
a.name,
coalesce( tmpGrpCat.email, '' ) as email,
coalesce( tmpGrpCat.codice_agente, '' ) as codice_agente,
coalesce( tmpGrpCat.codice_agente_fisso, '' ) as codice_agente_fisso,
coalesce( tmpGrpCat.codice_agente_VAR, '' ) as codice_agente_VAR,
coalesce( tmpGrpCat.cellulare, '' ) as cellulare,
coalesce( tmpGrpCat.AgTitle, '' ) as Agenzia,
coalesce( tmpGrpCat.RuoloTitle, '' ) as ruolo,
coalesce( tmpGrpCat.Canale, '' ) as Canale,
coalesce( tmpGrpCat.Area, '' ) as Area,
coalesce( tmpGrpCat.Status, '' ) as Status
FROM
joo_users as a
LEFT JOIN
( SELECT
up.user_id,
GROUP_CONCAT( DISTINCT IF( up.profile_key LIKE 'profile.agenzia_%', b.title, NULL )) AgTitle,
GROUP_CONCAT( DISTINCT IF( up.profile_key LIKE 'profile.ruolo_%', b.title, NULL )) RuoloTitle,
GROUP_CONCAT( DISTINCT IF( up.profile_key LIKE 'profile.canale_%' AND up.profile_value = '1',
REPLACE( up.profile_key, 'profile.canale_',''), NULL)) AS canale,
GROUP_CONCAT( DISTINCT IF( up.profile_key LIKE 'profile.area_%',
up.profile_value, NULL)) AS area,
GROUP_CONCAT( DISTINCT IF( up.profile_key LIKE 'profile.status_%',
up.profile_value, NULL)) AS status,
MAX( IF( up.profile_key = 'profile.email', up.profile_value, NULL) ) AS email,
MAX( IF( up.profile_key = 'profile.codice_agente', up.profile_value, NULL) ) AS codice_agente,
MAX( IF( up.profile_key = 'profile.codice_agente_fisso', up.profile_value, NULL) ) AS codice_agente_fisso,
MAX( IF( up.profile_key = 'profile.codice_agente_VAR', up.profile_value, NULL) ) AS codice_agente_VAR,
MAX( IF( up.profile_key = 'profile.cellulare', up.profile_value, NULL) ) AS cellulare
FROM
joo_user_profiles AS up
LEFT JOIN joo_usergroups AS b
ON REPLACE(up.profile_value, '"', '') = b.id
WHERE
( up.profile_key LIKE 'profile.agenzia_%'
OR up.profile_key LIKE 'profile.ruolo_%'
OR up.profile_key LIKE 'profile.canale_%'
OR up.profile_key LIKE 'profile.area_%'
OR up.profile_key LIKE 'profile.status_%'
OR up.profile_key = 'profile.email'
OR up.profile_key = 'profile.codice_agente'
OR up.profile_key = 'profile.codice_agente_fisso'
OR up.profile_key = 'profile.codice_agente_VAR'
OR up.profile_key = 'profile.cellulare' )
AND up.profile_value != '""'
AND up.profile_value != '"0"'
GROUP BY
up.user_id ) as tmpGrpCat
ON a.id = tmpGrpCat.user_id
ORDER BY
id asc
LIMIT
0, 20