Re: Slow Left Outer Join
Jay Pipes wrote:
> 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;
>
Nope using MySQL 4.1.10
Subject
Views
Written By
Posted
7859
December 19, 2005 06:10PM
3111
December 19, 2005 08:40PM
2684
December 20, 2005 12:28PM
2575
December 20, 2005 01:09PM
2252
December 20, 2005 01:31PM
2214
December 20, 2005 03:41PM
2044
December 20, 2005 04:17PM
2236
December 20, 2005 04:27PM
3326
December 20, 2005 04:36PM
2009
December 20, 2005 05:20PM
Re: Slow Left Outer Join
1929
December 20, 2005 05:38PM
2067
December 20, 2005 05:49PM
2133
December 20, 2005 04:27PM
1783
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.