MySQL Forums
Forum List  »  Performance

Re: Slow Left Outer Join
Posted by: Jay Pipes
Date: December 19, 2005 08:40PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
7851
December 19, 2005 06:10PM
Re: Slow Left Outer Join
3107
December 19, 2005 08:40PM
2681
December 20, 2005 12:28PM
2573
December 20, 2005 01:09PM
2248
December 20, 2005 01:31PM
2209
December 20, 2005 03:41PM
2041
December 20, 2005 04:17PM
2232
December 20, 2005 04:27PM
3324
December 20, 2005 04:36PM
2007
December 20, 2005 05:20PM
1928
December 20, 2005 05:38PM
2063
December 20, 2005 05:49PM
2131
December 20, 2005 04:27PM
1781
December 23, 2005 02:32PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.