MySQL Forums
Forum List  »  Performance

Re: Slow Left Outer Join
Posted by: Jay Pipes
Date: December 20, 2005 04:27PM

OK, not sure if this is "the textbook" way to solve this problem, but this *should* give you a correct result and a *very* fast return, if I'm not mistaken. What I've done is run INNER JOINs to get the "real" reports, and UNIONed a smaller, LEFT JOINed resultset for the "fake" reports... Tell me whether this will work for you:

(
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
)
UNION ALL
(
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
)
ORDER BY ProductionReportID;

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 04:28PM by Jay Pipes.

Options: ReplyQuote


Subject
Views
Written By
Posted
7851
December 19, 2005 06:10PM
3108
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
Re: Slow Left Outer Join
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.