Is EXISTS more efficient than COUNT(*)>0?

mysql performance exists
Ken picture Ken · Mar 10, 2011 · Viewed 15.4k times · Source

I'm using MySQL 5.1, and I have a query that's roughly of the form:

select count(*) from mytable where a = "foo" and b = "bar";

In my program, the only thing that it checks is whether this is zero or nonzero. If I convert this into:

select exists(select * from mytable where a = "foo" and b = "bar");

is MySQL smart enough to stop searching when it hits the first one? Or is there some other way to communicate to MySQL that my intent is simply to find out if any records match this, and I don't need an exact count?

Answer

Thomas picture Thomas · Mar 10, 2011

Yes, MySQL (indeed all database systems as far as I'm aware) will stop processing when a row is returned when using an Exists function.

You can read more at the MySQL documentation: If a subquery returns any rows at all, EXISTS subquery is TRUE.

Related questions

Best way to test if a row exists in a MySQL table

I'm trying to find out if a row exists in a table. Using MySQL, is it better to do a query like this: SELECT COUNT(*) AS total FROM table1 WHERE ... and check to see if the total is non-zero or …

Read More
MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET

What is main difference between INSERT INTO table VALUES .. and INSERT INTO table SET? Example: INSERT INTO table (a, b, c) VALUES (1,2,3) INSERT INTO table SET a=1, b=2, c=3 And what about performance of these two?

Read More
MySQL vs MongoDB 1000 reads

I have been very excited about MongoDb and have been testing it lately. I had a table called posts in MySQL with about 20 million records indexed only on a field called 'id'. I wanted to compare speed with MongoDB and …

Read More