Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 320
Ken Avatar asked Mar 10 '11 19:03

Ken


People also ask

Why exists () is faster than Count ()?

Answer: Using the T-SQL EXISTS keyword to perform an existence check is almost always faster than using COUNT(*). EXISTS can stop as soon as the logical test proves true, but COUNT(*) must count every row, even after it knows one row has passed the test.

Which is better in or exists?

IN works faster than the EXISTS Operator when If the sub-query result is small. If the sub-query result is larger, then EXISTS works faster than the IN Operator.

Which is faster count (*) or Count 1?

The simple answer is no – there is no difference at all. The COUNT(*) function counts the total rows in the table, including the NULL values. The semantics for COUNT(1) differ slightly; we'll discuss them later. However, the results for COUNT(*) and COUNT(1) are identical.

Why exists is faster in SQL?

“EXISTS” clause is preferred when there is a need to check the existence of values in another table or when there is a need to check against more than one column. The EXISTS clause is faster than IN when the subquery results are very large. The IN clause is faster than EXISTS when the subquery results are very small.


3 Answers

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.

like image 198
Thomas Avatar answered Sep 19 '22 21:09

Thomas


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.

like image 23
linepogl Avatar answered Sep 20 '22 21:09

linepogl


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.

like image 35
tc. Avatar answered Sep 16 '22 21:09

tc.