MySQL Forums
Forum List  »  Optimizer & Parser

Please help optimizing a long running query (left outer join, with 2 subqueries)
Posted by: arif bandali
Date: December 31, 2010 01:19AM

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!

Options: ReplyQuote


Subject
Views
Written By
Posted
Please help optimizing a long running query (left outer join, with 2 subqueries)
2462
December 31, 2010 01:19AM


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.