Re: Query Optimization Help

[Rick James]

Let's play a trick. If you SUM(RecordType) you get 1 for the ones you want, 2 can't happen?, or 3 if both rows are there. Assuming that will let us avoid the self join and instead use a GROUP BY.

Let's play another trick to avoid hitting the other table twice per result. Let's do the GROUP BY in a subquery that generates a tmp table. (Or you could do it as a separate step.)

SELECT d.OrderNumber,
       d.PhaseNumber,
       d.PartNumber,
       m.IssueDate
   FROM ( SELECT
                JobOrderNumber,
                OrderNumber,
                PhaseNumber,
                PartNumber
             FROM Job_Order_Detail
             GROUP BY JobOrderNumber, PhaseNumber, PartNumber,
                      IssueDate, LineItem
             HAVING SUM(RecordType) = 1
        ) AS d
   JOIN Job_Order_Master AS m
     ON m.JobOrderNumber = d.JobOrderNumber
   WHERE m.IssueDate > d.OpenDate

If this does not suffice, please provide
* SHOW CREATE TABLE tbl\G
* SHOW TABLE STATUS LIKE 'tbl'\G
* EXPLAIN SELECT ...\G
and surround them with [ code ] and [ / code ]