Group concat Query Performance-CodePudding

I have one query which almost took 22 second to populate data.

SELECT 
    TP.*, 
    GROUP_CONCAT(DISTINCT TS.`subject_name`) AS subjects, 
    GROUP_CONCAT(DISTINCT TC.`class_name`) AS classes, 
    GROUP_CONCAT(DISTINCT TT.`tution_name`) AS tution_type, 
    GROUP_CONCAT(DISTINCT TL.`name`) AS locations 
FROM `tutor_profile` TP 
LEFT JOIN `tutor_to_subject` TTS ON TP.`tutor_id`=TTS.`tutor` 
LEFT JOIN `tutor_subjects` TS ON TS.`subject_id`=TTS.`subject` 
LEFT JOIN `tutor_to_class` TTC ON TP.`tutor_id`=TTC.`tutor` 
LEFT JOIN `tutor_classes` TC ON TC.`class_id`=TTC.`class` 
LEFT JOIN `tutor_to_tution_type` TTTT ON TP.`tutor_id`=TTTT.`tutor` 
LEFT JOIN `tution_types` TT ON TT.`tution_id`=TTTT.`tution_type` 
LEFT JOIN `tutor_to_locality` TTL ON TP.`tutor_id`=TTL.`tutor` 
LEFT JOIN `tutor_locality` TL ON TL.`id`=TTL.`locality` 
WHERE 1=1 AND TP.`status` = 1 
GROUP BY TP.`tutor_id` 
ORDER BY TP.`date_added` DESC LIMIT 0 , 25

is there anyway to improve its performance?

CodePudding user response：

You may try to use correlated subqueries in the output list instead of JOIN and GROUP BY:

SELECT 
    TP.*, 
    ( SELECT GROUP_CONCAT(DISTINCT TS.`subject_name`)
      FROM `tutor_to_subject` TTS 
      LEFT JOIN `tutor_subjects` TS ON TS.`subject_id`=TTS.`subject`
      WHERE TP.`tutor_id`=TTS.`tutor` ) AS subjects, 
-- the same for another output columns
FROM `tutor_profile` TP 
WHERE 1=1 AND TP.`status` = 1 
ORDER BY TP.`date_added` DESC LIMIT 0 , 25

CodePudding user response：

Replace these

GROUP_CONCAT(DISTINCT TS.`subject_name`) AS subjects, 

LEFT JOIN `tutor_to_subject` TTS ON TP.`tutor_id`=TTS.`tutor` 
LEFT JOIN `tutor_subjects` TS ON TS.`subject_id`=TTS.`subject`

with

( SELECT GROUP_CONCAT(DISTINCT TS.`subject_name`
    FROM `tutor_to_subject` TTS
    JOIN `tutor_subjects` TS ON TS.`subject_id` = TTS.`subject`
    WHERE  TP.`tutor_id` = TTS.`tutor`  
) AS subjects,

and toss the JOIN for TS. Do likewise for the other 3 group_concat Left join.

For many-to-many tables, improve the indexes:

CREATE TABLE tutor_to_subject
    tutor_id ...,
    subject_id ...,
    PRIMARY KEY(tutor_id, subject_id),
    INDEX(subject_id)
) ENGINE=InnoDB;

More discussion: Many:many mapping

Also, get rid of the GROUP BY since it should not longer be needed.

And add

INDEX(status, date_added)