MySQL Forums
Forum List  »  Performance

Performance of WHERE EXISTS query
Posted by: Dennis Gascoigne
Date: September 02, 2009 06:24PM

I am using a collection of persistent objects as a datalayer to mySQL and as a result, the format of my queries is out of my hands (if I continue to use it).

Where I would use the query;
SELECT distinct t.TranscriptID,AccessionID FROM transcript t inner join transcript_block b on t.transcriptid=b.transcriptid where contextID=2;

it creates the query;
select N0.TranscriptID,AccessionID from `transcript` N0 where exists(select * from `transcript_block` N1 where ((N0.`TranscriptID` = N1.`TranscriptID`) and (N1.`ContextID` = 2)))

As expected, the results are identical, however the time to retrieve the records is not. The former executes 40513 rows fetched in 0.0769s(0.3315s) and the latter 40513 rows fetched in 9.9409s(0.2249s).

Why the huge difference in performance (database is local) and what can I do to get the "Where exists" query records in a comparable duration to my INNER JOIN.

I am using INNODB. Any help would be appreciated. Thanks

Options: ReplyQuote

Written By
Performance of WHERE EXISTS query
September 02, 2009 06:24PM
September 04, 2009 07:29PM

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.