Performance issue with mysql query-CodePudding

I am working in MYSQL for the first and I am having issues with the following query

SELECT
    t.id,
    t.name,
    t.description,
    
    (
        SELECT
            GROUP_CONCAT( CONCAT( hs.name, '|', s.rate ) )
        FROM
            occupation_skill_rate s 
            INNER JOIN hard_skills hs ON s.hard_skill_id = hs.id
        WHERE
            s.occupation_id = t.id
        ORDER BY
            s.rate DESC LIMIT 15
    ) AS skills,

    (
        SELECT
            GROUP_CONCAT( CONCAT( hs.name, '|', s.rate ) )
        FROM
            occupation_knowledge_rate s 
            INNER JOIN knowledge hs ON s.knowledge_id = hs.id
        WHERE
            s.occupation_id = t.id
        ORDER BY
            s.rate DESC LIMIT 15
    ) AS knowledge,

    (
        SELECT
            GROUP_CONCAT( CONCAT( hs.name, '|', s.rate ) )
        FROM
            occupation_abilities_rate s 
            INNER JOIN ability hs ON s.ability_id = hs.id
        WHERE
            s.occupation_id = t.id
        ORDER BY
            s.rate DESC LIMIT 15
    ) AS knowledge

FROM
    occupations t

The occupation table contains 1033 rows occupation_skill_rate contains 34160 rows and it takes more than 1 minute to execute this query. Please let me know if you need further clarification for helping me.

Thanks for your help Ajai

CodePudding user response：

The occupation_%_rate tables seem to be many-to-many, correct? They need these indexes and no id:

PRIMARY KEY(occupation_id, xxx_id)
INDEX(xxx_id, occupation_id)

But, it seems like the ORDER BY and LIMIT when used with GROUP_CONCAT(). Please describe what the query's intention is; we may be able to help in rewriting it.

GROUP_CONCAT allows an ORDER BY clause but not a LIMIT. Can you do without the LIMITs?

Example

Instead of

    ( SELECT  GROUP_CONCAT( CONCAT( hs.name, '|', s.rate ) )
            FROM  occupation_skill_rate s
            INNER JOIN  hard_skills hs  ON s.hard_skill_id = hs.id
            WHERE  s.occupation_id = t.id
            ORDER BY  s.rate DESC
            LIMIT  15 
    ) AS skills;

    ( SELECT  CONCAT( name, '|', rate ORDER BY  rate DESC )
            FROM (
                SELECT  hs.name, s.rate
                    FROM  occupation_skill_rate s
                    INNER JOIN  hard_skills hs
                          ON s.hard_skill_id = hs.id
                         AND s.occupation_id = t.id
                    ORDER BY  s.rate DESC
                    LIMIT  15
                 ) AS a 
    ) AS skills

But I suspect t is not visible that deeply nested.

If that is the case, rearrange things thus:

SELECT  t.id, t.name, t.description, s3.skills, ...
    FROM  occupations AS t
    JOIN (
        SELECT  s2.occupation_id,
                CONCAT( s2.name, '|', s2.rate ORDER BY  rate DESC )
                      AS skills
            FROM (
                SELECT  hs.name, s1.rate, s1.occupation_id
                    FROM  occupation_skill_rate s1
                    INNER JOIN  hard_skills hs
                           ON s1.hard_skill_id = hs.id
                    ORDER BY  s.rate DESC
                    LIMIT  15 
                 ) AS s2
            GROUP BY  s2.occupation_id
            ORDER BY  s2.rate DESC
         ) AS s3  ON s2.occupation_id = t.id
    JOIN  ...
    JOIN  ... ;

Another

There is also a way to build the long GROUP_CONCAT, then chop to 15 items by using SUBSTRING_INDEX(...).