how can i improve the performance of this joomla query?

Question

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 ?

Those join conditions are horrendous. As long as you have those, you're stuck with lousy performance. — Strawberry, Jan 28 '14 at 18:37

score 1 · Accepted Answer · edited Jan 29 '14 at 11:56

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

i did observe a 10x improvement on my local machine but for some reason when i run the quey on the server it hangs indefinitely (after some minutes i've stopped it) my dev machine is mysql 5.6 while the server is 5.5 but i doubt that could be the issue. — G3z, Jan 29 '14 at 12:00

score 0 · Answer 2 · answered May 09 '14 at 09:39

Since you are executing this query in Joomla you can try Tabulizer for Joomla (http://wwww.tabulizer.com) that can handle big data sets by using a data source cache (for your query results) and ajax loading, so only the first page of your table is fetched initially and rest of the data only when needed. Of course, the query needs to execute at least once for the cache to get populated, so you have to either improve it further or increase the available system resources (PHP memory and max execution time). This is most likely why it fails on the server and not on the local server, as most shared hosting environments have a max execution time for your PHP script 30 seconds.

how can i improve the performance of this joomla query?

2 Answers2