mysql - 优化MySQL查询:以下

Question

我是mysql新手，我必须减少以下更新查询的执行时间

UPDATE temp_countcalculations,
(
    SELECT count(*) as insuffcounts,CRP_RefNo as ref
    FROM testsymphony7.p_education 
    WHERE testsymphony7.p_education.EducationStatusId=6 
    GROUP BY CRP_RefNo
) as icounts
SET Edu_pending=icounts.insuffcounts
WHERE temp_countcalculations.crp_refno=icounts.ref;

Answer 1

根据您提供的有限信息，我将尽可能扩展您的可能性(如果您还没有这样做)

首先，让我们重写您的查询:

UPDATE 
    temp_countcalculations t
        JOIN (
            SELECT 
                COUNT(*) as insuffcounts,
                CRP_RefNo as ref
            FROM 
                testsymphony7.p_education p
            WHERE 
                p.EducationStatusId = 6 
            GROUP BY 
                CRP_RefNo
        ) i ON t.crp_refno = icounts.ref
SET 
    t.Edu_pending = i.insuffcounts;

很好。

因此，您将根据引用来更新所有 t.Edu_pending 和 i.insuffcounts。这里有 2 个查询需要优化。

(1):

SELECT 
    COUNT(*) AS insuffcounts,
    CRP_RefNo as ref
FROM 
    testsymphony7.p_education p
WHERE 
    p.EducationStatusId = 6 
GROUP BY 
    CRP_RefNo

和(2):

SELECT 1 
FROM
    temp_countcalculations t
        JOIN ((1)) i ON t.crp_refno = icounts.ref

优化 (1):

• 列上的理想索引:CRP_RefNo、EducationStatusId(复合)
• 列testsymphony7.p_education.crp_refno NOT NULL，如果可能，UNIQUE。

优化(2):

• 列上的理想索引temp_countcalculations.crp_refno
• 列temp_countcalculations.crp_refno NOT NULL，如果可能，UNIQUE。

基于此，我们也许可以进一步了解您的结果:

EXPLAIN 
SELECT 1 
FROM 
    temp_countcalculations t
        JOIN (
            SELECT 
                COUNT(*) as insuffcounts,
                CRP_RefNo as ref
            FROM 
                testsymphony7.p_education p
            WHERE 
                p.EducationStatusId = 6 
            GROUP BY 
                CRP_RefNo
        ) i ON t.crp_refno = icounts.ref