Performance issue with mysql query

Question

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

See those ugly red boxes with "Full table scan" in them? That's why your query is slow. What INDEX objects do you have defined on those tables? Please show the full EXPLAIN output as that will show you what indexes you need to create. — Dai
– Dai, Commented Oct 8, 2022 at 8:44
Yeah you are correcr i added the index for occcupation_id column and now the full query runs under 1s approx 0.600s — Ajai rajan
– Ajai rajan, Commented Oct 8, 2022 at 9:40

Rick James · Accepted Answer · 2022-10-09 15:51:15Z

1

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;

Do

    ( 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(...).

edited Oct 9, 2022 at 15:51

answered Oct 8, 2022 at 20:02

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ajai rajan Over a year ago

Yes, i understand we can't use limit in a subquery. Does any other workaround? needs only 15 top rows from that subquery

Rick James Over a year ago

I added more...

Collectives™ on Stack Overflow

Performance issue with mysql query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related