Re: Slow Left Outer Join

[Jay Pipes]

You are using a version of MySQL < 4? I'll answer for you...yes. :)

Try this workaround:

CREATE TEMPORARY TABLE tmpResult
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'
INNER 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;

INSERT INTO tmpResult
SELECT PR.ReportDate
, PR.WorkDay
, PRS.ShiftName
, NULL AS TotalPrice
, PR.ProductionReportID
, NULL 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
WHERE PR.ReportDate BETWEEN '2005-12-01' AND '2005-12-31'
AND PRU.ProductionReportID IS NULL # here, we include only the fake reports...
GROUP BY
PR.ProductionReport_ShiftID
, PR.ReportDate;

SELECT * FROM tmpResult
ORDER BY ProductionReportID;

DROP TABLE tmpResult;

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

Got Cluster? http://www.mysql.com/cluster
Personal: http://jpipes.com



Edited 1 time(s). Last edit at 12/20/2005 05:26PM by Jay Pipes.