Please help optimizing a long running query (left outer join, with 2 subqueries)
Hi all.
The query I need help with is:
SELECT d.bn, d.4700, d.4500, ... , p.`Activity Description`
FROM
( SELECT temp.bn, temp.4700, temp.4500, ....
FROM `tdata` temp
GROUP BY temp.bn
HAVING (COUNT(temp.bn) = 1) ) d
LEFT OUTER JOIN
( SELECT temp2.bn, max(temp2.FPE) AS max_fpe, temp2.`Activity Description`
FROM `pdata` temp2
GROUP BY temp2.bn ) p
ON p.bn = d.bn;
The ... represents other fields that aren't really important to solving this problem.
The issue is on the the second subquery - it is not using the index I have created and I am not sure why, it seems to be because of the way TEXT fields are handled. The first subquery uses the index I have created and runs quite snappy, however an explain on the second results in a 'Using temporary; Using filesort'. Please see the indexes I have created in the below table create statements. Can anyone help me optimize this?
By way of quick explanation the first subquery is meant to only select records that have unique bn's, the second, while it looks a bit wacky (with the max function there which is not being used in the result set) is making sure that only one record from the right part of the join is included in the result set.
My table create statements are
CREATE TABLE `tdata` (
`BN` varchar(15) DEFAULT NULL,
`4000` varchar(3) DEFAULT NULL,
`5800` varchar(3) DEFAULT NULL,
....
KEY `BN` (`BN`),
KEY `idx_t3010` (`BN`,`4700`,`4500`,`4510`,`4520`,`4530`,`4570`,`4950`,`5000`,`5010`,`5020`,`5050`,`5060`,`5070`,`5100`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
CREATE TABLE `pdata` (
`BN` varchar(15) DEFAULT NULL,
`FPE` datetime DEFAULT NULL,
`Activity Description` text,
....
KEY `BN` (`BN`),
KEY `idx_programs_2009` (`BN`,`FPE`,`Activity Description`(100))
) ENGINE=MyISAM DEFAULT CHARSET=utf8
Thanks!