Re: Please help optimizing a long running query (left outer join, with 2 subqueries)

[Rick James]

ON p.bn = d.bn
Neither of these is indexed. That means table scans.

One approach is to get one of the subqueries indexed as you build it:

CREATE TEMPORARY TABLE p (
    bn ...,
    PRIMARY KEY (bn)
)
    SELECT  temp2.bn, max(temp2.FPE) AS max_fpe,
                temp2.`Activity Description`
            FROM  `pdata` temp2
            GROUP BY  temp2.bn ;