mysql - 使用 COUNT 和 LEFT JOIN 的慢速 MYSQL 查询

Question

我正在使用以下查询 COUNT 记录并在 MySQL 中显示记录。虽然它显示良好并且工作正常，但速度非常慢。我认为这部分是由于 HTG_ScheduleRequest 表非常大。有没有更好的方法来安排此查询以加快速度？

SELECT HTG_TechProps.EmpNumber, 
       HTG_TechProps.EmpFirstName, 
       HTG_TechProps.EmpLastName,  
       HTG_TechProps.Veh_Number,
       COUNT(HTG_ScheduleRequest.ID) AS current_job 
    FROM HTG_TechProps 
        LEFT JOIN HTG_ScheduleRequest 
            ON HTG_TechProps.EmpNumber = HTG_ScheduleRequest.SSR 
                AND (HTG_ScheduleRequest.ScheduleDateCurrent = CURDATE()) 
                AND HTG_ScheduleRequest.JobStatus = '3' 
    GROUP BY HTG_TechProps.EmpNumber 
    ORDER BY HTG_TechProps.EmpNumber ASC

Answer 1

假设 EmpNumber 是唯一的，请尝试此版本(索引跟随查询):

SELECT tp.EmpNumber, tp.EmpFirstName, tp.EmpLastName, tp.Veh_Number,
       (SELECT COUNT(*)
        FROM HTG_ScheduleRequest sr
        WHERE tp.EmpNumber = sr.SSR AND
              sr.ScheduleDateCurrent = CURDATE() AND
              sr.JobStatus = '3'
       )
FROM HTG_TechProps  tp
ORDER BY tp.EmpNumber ASC;

在运行查询之前，创建以下索引:HTG_TechProps(EmpNumber) 和 HTG_ScheduleRequest(SSR, JobStatus, ScheduleDateCurrent)。