First create an index on the ReportDate field:
CREATE INDEX ix_ReportDate ON ProductionReport (ReportDate);
Then, ensure you have an index on ProductionReport_Unit (ProductionReportID)
Then, please post the EXPLAIN output of the following:
SELECT PR.ReportDate
, PR.WorkDay
, PRS.ShiftName
, SUM((PRU.UnitsCompleted/JR.TotalUnits)*B.Price) AS TotalPrice
, PR.ProductionReportID
, (SUM((PRU.UnitsCompleted/JR.TotalUnits)*B.Price)/(PR.NumSawyers +
PR.NumBuilders + PR.NumForkliftDrivers + PR.NumShipping)/(PR.NumHours)) AS HourlyRate
FROM ProductionReport PR
INNER JOIN ProductionReport_Shift PRS
ON PR.ProductionReport_ShiftID = PRS.ProductionReport_ShiftID
AND PRS.ProductionLocationID = '2'
LEFT JOIN ProductionReport_Unit PRU
ON PR.ProductionReportID = PRU.ProductionReportID
INNER JOIN JobRelease JR
ON PRU.JobReleaseID = JR.JobReleaseID
INNER JOIN Job J
ON JR.JobID = J.JobID
INNER JOIN Batch B
ON JR.JobReleaseID = B.JobReleaseID
WHERE PR.ReportDate BETWEEN '2005-12-01' AND '2005-12-31'
GROUP BY
PR.ProductionReport_ShiftID
, PR.ReportDate
ORDER BY PR.ProductionReportID;
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com