mysql - 使用计数(不同)查询运行选择时如何加快 mysql 查询

Question

有人可以告诉我如何使用加速这个 mysql 查询吗？我知道由于 count(distinct subcontractorRef) 语法，它目前运行缓慢。还有其他选择吗？

SELECT DISTINCT (contractorsRef) AS cref, RIDGROUP AS ridg,  
    (select count(DISTINCT subcontractorRef) 
    FROM request 
    INNER JOIN request_config ON request_config.RIDGROUP = request.RIDGROUP 
    WHERE request_config.contractorsRef = outer_config.contractorsRef  
    AND currenttaxyear =2011 
    AND weekno =31) AS xxx 
FROM request_config outer_config 
WHERE currenttaxyear =2011 
AND weekno =32 
AND contractorsRef <>132 

Answer 1

尝试使用 JOIN 而不是子查询:

SELECT
    contractorsRef AS cref,
    RIDGROUP AS ridg,
    IFNULL(T1.subcontractorCount, 0) AS xxx
FROM request_config AS outer_config
LEFT JOIN
(
    SELECT
        request_config.contractorsRef,
        COUNT(DISTINCT subcontractorRef) AS subcontractorCount
    FROM request 
    INNER JOIN request_config
    ON request_config.RIDGROUP = request.RIDGROUP 
    AND currenttaxyear = 2011 
    AND weekno = 31
    GROUP BY contractorsRef
) T1
ON T1.contractorsRef = outer_config.contractorsRef 
WHERE currenttaxyear = 2011 
AND weekno = 32 
AND contractorsRef <> 132
GROUP BY outer_config.contractorsRef