Take Long Time To Load Group By Query in MySQL

Its My Query

select date_format(edate, '%d-%b-%Y') as EnteredDate,shiftno as ShiftNo,case pmgcode when 1 then 'Cutting Upper ' when 4 then

'Full Shoe Process' when 5 then 'Preparaition & Closing' end as 'Process',conveyor as Line,Factory,FactoryQC,CustomerQC,case

pmgcode when 1 then 260 when 4 then 350 end TargetScore,(select(count(*)) from processtrans a where date(a.edate) = pt.edate

and a.shiftno = pt.shiftNo and batch is null and a.etime = pt.etime and a.factoryQc=pt.factoryQc and a.CustomerQC=pt.CustomerQC

and a.comment =pt.comment and a.article=pt.article and a.ptype = 'N/A')as NAOperation,((select(count(*)) from processtrans a

where date(a.edate) = pt.edate and a.shiftno = pt.shiftNo and batch is null and a.etime = pt.etime and a.factoryQc=pt.factoryQc

and a.CustomerQC=pt.CustomerQC and a.comment =pt.comment and a.article=pt.article and a.ptype = 'N/A')*5) as NAScore,case pmgcode

when 1 then 260 when 4 then 350 end - ((select(count(*)) from processtrans a where date(a.edate) = pt.edate and

a.shiftno = pt.shiftNo and batch is null and a.etime = pt.etime and a.ptype = 'N/A' and a.factoryQc=pt.factoryQc and

a.CustomerQC=pt.CustomerQC and a.comment =pt.comment and a.article=pt.article)*5) TotalScore, sum(marks) AuditScore,

(select distinct ReducePercent from ProcessTrans a where date(a.edate) = pt.edate and a.shiftno = pt.shiftNo and

batch is null and a.etime = pt.etime and a.factoryQc=pt.factoryQc and a.CustomerQC=pt.CustomerQC and a.comment=pt.comment and

a.article=pt.article) as ReducePercent, round(( sum(marks) /(case pmgcode when 1 then 260 when 4 then 350 end -

((select(count(*)) from processtrans a where date(a.edate) = pt.edate and a.shiftno = pt.shiftNo and batch is null and

a.etime = pt.etime and a.factoryQc=pt.factoryQc and a.CustomerQC=pt.CustomerQC and a.comment=pt.comment and a.article=pt.article

and a.ptype = 'N/A') * 5))*100)-(select distinct ReducePercent from ProcessTrans a where date(a.edate) = pt.edate

and a.shiftno = pt.shiftNo and batch is null and a.etime = pt.etime and a.factoryQc=pt.factoryQc and a.CustomerQC=pt.CustomerQC

and a.comment=pt.comment and a.article=pt.article) ,2) as AuditPer,pt.eTime time from processtrans pt where batch is null and

date(edate)>='2021-03-01' and date(edate)<='2021-04-09' and factory='A'and Conveyor='1'and shiftNo='1'

group by pt.shiftno,pt.pmgcode,pt.conveyor,pt.factoryqc,pt.customerqc,pt.etime,pt.edate

order by edate,shiftno,conveyor,ReducePercent asc

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

mohaammed talha5y ago
Its My Query

select date_format(edate, '%d-%b-%Y') as EnteredDate,shiftno as ShiftNo,case pmgcode when 1 then 'Cutting Upper ' when 4 then
'Full Shoe Process' when 5 then 'Preparaition & Closing' end as 'Process',conveyor as Line,Factory,FactoryQC,CustomerQC,case
pmgcode when 1 then 260 when 4 then 350 end TargetScore,(select(count(*)) from processtrans a where date(a.edate) = pt.edate
and a.shiftno = pt.shiftNo and batch is null and a.etime = pt.etime and a.factoryQc=pt.factoryQc and a.CustomerQC=pt.CustomerQC
and a.comment =pt.comment and a.article=pt.article and a.ptype = 'N/A')as NAOperation,((select(count(*)) from processtrans a
where date(a.edate) = pt.edate and a.shiftno = pt.shiftNo and batch is null and a.etime = pt.etime and a.factoryQc=pt.factoryQc
and a.CustomerQC=pt.CustomerQC and a.comment =pt.comment and a.article=pt.article and a.ptype = 'N/A')*5) as NAScore,case pmgcode
when 1 then 260 when 4 then 350 end - ((select(count(*)) from processtrans a where date(a.edate) = pt.edate and
a.shiftno = pt.shiftNo and batch is null and a.etime = pt.etime and a.ptype = 'N/A' and a.factoryQc=pt.factoryQc and
a.CustomerQC=pt.CustomerQC and a.comment =pt.comment and a.article=pt.article)*5) TotalScore, sum(marks) AuditScore,
(select distinct ReducePercent from ProcessTrans a where date(a.edate) = pt.edate and a.shiftno = pt.shiftNo and
batch is null and a.etime = pt.etime and a.factoryQc=pt.factoryQc and a.CustomerQC=pt.CustomerQC and a.comment=pt.comment and
a.article=pt.article) as ReducePercent, round(( sum(marks) /(case pmgcode when 1 then 260 when 4 then 350 end -
((select(count(*)) from processtrans a where date(a.edate) = pt.edate and a.shiftno = pt.shiftNo and batch is null and
a.etime = pt.etime and a.factoryQc=pt.factoryQc and a.CustomerQC=pt.CustomerQC and a.comment=pt.comment and a.article=pt.article
and a.ptype = 'N/A') * 5))*100)-(select distinct ReducePercent from ProcessTrans a where date(a.edate) = pt.edate
and a.shiftno = pt.shiftNo and batch is null and a.etime = pt.etime and a.factoryQc=pt.factoryQc and a.CustomerQC=pt.CustomerQC
and a.comment=pt.comment and a.article=pt.article) ,2) as AuditPer,pt.eTime time from processtrans pt where batch is null and
date(edate)>='2021-03-01' and date(edate)<='2021-04-09' and factory='A'and Conveyor='1'and shiftNo='1'
group by pt.shiftno,pt.pmgcode,pt.conveyor,pt.factoryqc,pt.customerqc,pt.etime,pt.edate
order by edate,shiftno,conveyor,ReducePercent asc
Salman Beg5y ago
In that case you need to check the query and need to find out is there any unnecessary joins exists. Check primary key exists or not etc. https://www.bridge-global.com/blog/tips-to-improve-mysql-query-performance/ https://www.infoworld.com/article/3210905/10-essential-performance-tips-for-mysql.html https://dzone.com/articles/how-to-optimize-mysql-queries-for-speed-and-perfor
Rajanikant Hawaldar5y ago
post your query