I am new to mysql, I have to reduce the execution time of below update query
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;
Based on the limited informations you provided, I'll expand as good as I can the possibilities you have (if you did not already do it)
First, let's rewrite your query:
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;
Good.
So, you're updating all the t.Edu_pending with i.insuffcounts, based on their reference.
There are 2 queries to optimize here.
(1):
SELECT
COUNT(*) AS insuffcounts,
CRP_RefNo as ref
FROM
testsymphony7.p_education p
WHERE
p.EducationStatusId = 6
GROUP BY
CRP_RefNo
and (2):
SELECT 1
FROM
temp_countcalculations t
JOIN ((1)) i ON t.crp_refno = icounts.ref
Optimizing (1):
CRP_RefNo, EducationStatusId (composite)testsymphony7.p_education.crp_refno NOT NULL and if possible, UNIQUE.Optimizing (2):
temp_countcalculations.crp_refnotemp_countcalculations.crp_refno NOT NULL and if possible, UNIQUE.Based on that, we might be able to go a bit further with your result of:
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
SHOW CREATE TABLE statements I mentionned in the results as well as the EXPLAIN I'm asking in the question, please.temp_countcalculations (n Crp_Refno varchar(50) CHARACTER SET utf8 NOT NULL,n Edu_Pending int(11) DEFAULT 0,n Emp_Pending int(11) DEFAULT 0,n Resi_Pending int(11) DEFAULT 0,n crim_Pending int(11) DEFAULT 0,n ref_Pending int(11) DEFAULT 0,n genuine_Pending int(11) DEFAULT 0,n court_Pending int(11) DEFAULT 0,n database_check_Pending int(11) DEFAULT 0,n Identity_Pending int(11) DEFAULT 0,n Total_Pending int(11) DEFAULT 0,n Total_Check int(11) DEFAULT 0n) ENGINE=InnoDB DEFAULT CHARSET=latin1p_education (n CRP_RefNo varchar(50) NOT NULL,n EducationID int(11) NOT NULL,n EducationPassingYear_Auto date DEFAULT NULL,n ReportId int(11) DEFAULT NULL,n SubordinateCheckId int(11) DEFAULT 0,n PRIMARY KEY (CRP_RefNo,EducationID),n KEY FK_ReportIdEducation_idx (ReportId),n CONSTRAINT FK_ReportIdEducation FOREIGN KEY (ReportId) REFERENCES m_reporttype (ReportId) ON DELETE NO ACTION ON UPDATE NO ACTIONn) ENGINE=InnoDB DEFAULT CHARSET=latin1
SHOW CREATE TABLE temp_countcalculationsANDSHOW CREATE TABLE testsymphony7.p_education