Take Long Time To Load Group By Query in MySQL

Question

when I want execute a query in my sql it take long time to execute on particular table

mohaammed talha · Answer

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) group by pt.shiftno,pt.pmgcode,pt.conveyor,pt.factoryqc,pt.customerqc,pt.etime,pt.edate order by edate,shiftno,conveyor,ReducePercent asc

Salman Beg · Answer

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 Hawaldar · Answer

post your query