MySQL Forums
Forum List  »  Optimizer & Parser

EXISTS() optimization
Posted by: Rob Blick
Date: June 07, 2006 10:00AM

Does anyone know if EXISTS() is optimized to return TRUE as soon as the first row in the subquery is encountered? I have developers who what to know whether a particular row exists in a table. Two ways to write it:

SELECT count(*) FROM TABLE WHERE column=value;

or

SELECT EXISTS (SELECT * FROM TABLE WHERE column=value);

In the first, we know a row exists if the query returns a number > 0. In the second, we get a true or false. Intuitively, the first may do more work than the second, especially if the where clause isn't limiting to a unique key, because it has to count ALL rows that meet the where condition. In the second, it's theoretically possible for the subquery to stop as soon as it hits the first matching record, because EXISTS can return true at that point. We have a mix of MyISAM and InnoDB tables. Any thoughts on whether EXISTS() DOES actually stop once the first matching record is hit?

Thanks!
Rob

Options: ReplyQuote


Subject
Views
Written By
Posted
EXISTS() optimization
19616
June 07, 2006 10:00AM
6586
July 25, 2006 07:54AM
5608
July 25, 2006 01:13PM
5245
August 04, 2006 04:45AM


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.