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

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?

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.

I have run a test with 1000 queries. SELECT EXISTS was about 25% faster than SELECT COUNT. Adding limit 1 to SELECT COUNT did not make any difference.

The most reliable way is probably LIMIT 1, but that's not the point.

Provided you have an index like CREATE INDEX mytable_index_a_b ON mytable (a,b), MySQL should be smart enough to return the count from the index and not touch any rows at all. The benefit of LIMIT 1 is probably negligible.

If you don't have an index on (a,b), then performance will be terrible. LIMIT 1 may make it significantly less terrible, but it'll still be terrible.

This might be an approach too.

select 1 from mytable where a = "foo" and b = "bar" limit 1;

This would not traverce all records that meet the where condition but rather return '1' after first 'hit'. The drawback is you need to check for result, because there might be empty record set in return.

I don't know how well this works for optimization, but it should work functionally the same as exists. Exception being that it will return no row if there is no match.

SELECT true from mytable where a = "foo" and b = "bar" LIMIT 1;