时间戳

首页 > 解决方案 > 组连接查询性能

mysql - 组连接查询性能

问题描述

我有一个查询几乎需要 22 秒来填充数据。

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

有没有办法提高它的性能？

标签： mysqlperformance

解决方案

您可以尝试在输出列表中使用相关子查询，而不是 JOIN 和 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

推荐阅读