MYSQL Server going to 50%CPU when query run

[acadia]

Hi All,

 

I have a query below that when run causes my MySQL server to time out.  I was wondering if there was a better way for me to write it?  I am not asking you to do it for me just some hints maybe?

 

Regards,

Acadia

 

Select student.FirstName AS SFirst, 
student.Surname AS SSurname,  
student.MiddleName AS SMiddle, 
student.DateOfBirth AS SDOB,  
student.gender AS sgender, 
student.NZQANumber AS SNZQANumber, 
student.Address AS Saddress, 
student.StreetAddress AS SStreetAddress, 
student.City AS SCity, 
student.PostCode AS SPostcode, 
student.PhoneNumber AS Sphone, 
student.MobileNumber AS Smob, 
student.eMail AS Semail, 
student.Notes AS Snotes, 
student.IDNumber AS SIDNUMBER, 
student.Active AS Sactive, 
date_format(EnrollDate,'%%d-%%m-%%Y') AS EnrollDate1, 

trainer.FirstName AS Tfirst, 
trainer.Surname AS Tsurn, 
trainer.gender AS TGender, 
trainer.DateOfBirth AS TDOB,  
trainer.IDNumber AS TIDNumber, 
trainer.Address AS TAddress, 
trainer.StreetAddress AS TSTreet, 
trainer.City AS TCity, 
trainer.PostCode AS TPostcode, 
trainer.PhoneNumber AS Tphone, 
trainer.MobileNumber AS Tmob, 
trainer.eMail AS Temail,

Provider.ProviderID,
Provider.Name AS ProvName, 
Provider.Address AS provadress, 
Provider.StreetAddress AS provstreetaddress, 
Provider.City AS provcity, 
Provider.PostCode AS provpostcode, 
Provider.PhoneNumber AS provphonenumber, 
Provider.FaxNumber AS provfax, 
Provider.WebSite AS ProvWebsite,

providercontact.FirstName AS PFirst, 
providercontact.MiddleName AS PMidle, 
providercontact.Surname AS Psurname, 
providercontact.gender AS Pgender, 
providercontact.PhoneNumber AS PPhone, 
providercontact.MobileNumber AS PMob, 
providercontact.FaxNumber AS PFax,  
providercontact.eMail AS Pemail, 
providercontact.Active AS PActive, 
providercontact.Provider AS PProvider, 
providercontact.StreetAddress AS Pstreet, 
providercontact.Suburb AS PSuburb, 
providercontact.City AS Pcity, 
providercontact.POBox AS PPobox,

studentcourse1.StudentID,

studentcourse1.SCNZQANo,
SCNZQAUnitTitle, 
SCCredit,  
SCTrainingHours, 
SCAssesmentHours, 
SCAdminHours, 
SCTotalHours, 
SCCoursecomplete, 
SCCompetent, 
SCAssesSubmitDate, 
SCNZQAUnitStandardVersionSubmitted, 
SCNZQAUnitStandardVersion,

studentpurchaseorder.StudentPurchaseOrderID, 
studentpurchaseorder.PruchaseOrderNo AS SPPruchaseOrderNo, 
studentpurchaseorder.ClaimNo AS SPClaimNo, 
studentpurchaseorder.DateOfPO AS SPDateOfPO,

date_format(Invoice_Date,'%d-%m-%Y') AS SIInvoiceDate, 
studentinvoice.Number AS SINumber, 
studentinvoice.Hours AS SIHours, 
date_format(Date_Paid,'%d-%m-%Y') AS SIDate_Paid,

studenttrainingpt1.ACCTNA AS STACCTNA,
studenttrainingpt1.WINZTIA AS STWINZTIA,
studenttrainingpt1.TIAReceived AS STTiaRec,
studenttrainingpt1.TrainingLocation AS STTrainingLoc,
studenttrainingpt1.DateAssigned AS STDateAssigned,
studenttrainingpt1.DateAssessment AS STDAteAssessment,
studenttrainingpt1.DateAssessmentSentACC AS STDateAssesssentACC,
studenttrainingpt1.DateAssessmentRecACC AS STDateASSESSRecACC,
studenttrainingpt1.HoursApproved AS STHoursApproved,
studenttrainingpt1.HoursCompleted AS STHoursComplete,
studenttrainingpt1.CourseStartDate AS STCoursStartDate,
studenttrainingpt1.CourseCompleteDate AS STCourseCompleteDate




From student


JOIN studenttrainer ON (student.StudentID = studenttrainer.StudentID )

JOIN trainer ON (studenttrainer.TrainerID = trainer.TrainerID )

JOIN studentprovider ON (student.StudentID = studentprovider.StudentID )

JOIN provider ON ( studentprovider.ProviderID = provider.ProviderID )

JOIN studentcasemanager ON (student.StudentID = studentcasemanager.StudentID )

JOIN providercontact ON ( studentcasemanager.ProviderContactID = providercontact.ProviderContactID )

JOIN (SELECT 
StudentID,
studentcourse.NZQANo AS SCNZQANo,
studentcourse.NZQAUnitTitle AS SCNZQAUnitTitle, 
studentcourse.Credit AS SCCredit,  
studentcourse.TrainingHours AS SCTrainingHours, 
studentcourse.AssesmentHours AS SCAssesmentHours, 
studentcourse.AdminHours AS SCAdminHours, 
studentcourse.TotalHours AS SCTotalHours, 
studentcourse.CourseComplete AS SCCoursecomplete, 
studentcourse.Competent AS SCCompetent, 
studentcourse.AssesSubmitDate AS SCAssesSubmitDate, 
studentcourse.NZQAUnitStandardVersionSubmitted AS SCNZQAUnitStandardVersionSubmitted, 
studentcourse.NZQAUnitStandardVersion AS SCNZQAUnitStandardVersion from  studentcourse ) AS studentcourse1


ON ( student.StudentID = studentcourse1.StudentID )

JOIN studentinvoice ON ( student.StudentID = studentinvoice.StudentID )

JOIN studentpurchaseorder ON ( student.StudentID = studentpurchaseorder.StudentID )

JOIN studenttrainingpt1 ON (student.StudentID = studenttrainingpt1.StudentID )

JOIN (SELECT  traininghours.StudentID,traininghours.StudentCourseID, 
                      (IFNULL(SUM(traininghours.TrainingHours), 0) + IFNULL(SUM(traininghours.AssessmentHours), 0) + IFNULL(SUM(traininghours.AdminHours), 0)) AS train
  
                 FROM traininghours 
                GROUP BY traininghours.StudentID,traininghours.StudentCourseID) AS T2 

     ON studentcourse1.StudentID = T2.StudentID AND studentcourse1.SCNZQANo = T2.StudentCourseID

[premiso]

Thats a lot going on in 1 query. How often is this query ran / does it need to be ran?

 

It may be prudent to separate it out into some smaller queries if you use it quite often, as doing sums inside of subquerys I can see where that would be very CPU intense.

 

Depending on when it is displayed you may also be able to fetch some of the data once and store it in session (such as a single student viewing their information). Well not much help, but I would also check and make sure your indexes are correct.

 

[acadia]

Hi Premiso,

 

I realised  I hadnt used correct join syntax and now it is working perfectly and only take 1-2 seconds to run.

 

What I am trying to do is a) a user has a form with the option of a select box where they can choose to filter the information by "provider" and then only show the rows that are assigned to that provider.  This works great with

 WHERE Provider.ProviderID =".$_get["provider"]"

(This is on a local network not on the intertnet by the way so no SQL injections are going to happen)

 

However if a provider is not chosen I get a MySQL systax error and I am unsure how to get around this.

 

Do you have any idea?  Please ask if you require more information.

Regards,

Chris