Find if there is a line on table : count or first?

Question

I wish to do some optimizations on my SQL queries. Without doing a performance test for it, what query is the most time-consuming ? I am almost sure they return the same result.

!MyClass.find(:first, :conditions => c).nil?
MyClass.count(:conditions => c) > 0
MyClass.count(:conditions => c, :limit => 1) > 0

Regards

Why don't you do each one yourself and see how long it takes? — Jason Kim, May 03 '13 at 08:25
You can use .exists? I suppose it is the cheapest one. It returns "SELECT 1 FROM table_name WHERE ..." — Babur Ussenakunov, May 03 '13 at 08:50

score 0 · Answer 1 · answered May 03 '13 at 09:03

0

You can test it for your own. Writing a performance test with less then 15 lines of code. For more information:

http://guides.rubyonrails.org/performance_testing.html

answered May 03 '13 at 09:03

Mindbreaker

1,015
1
9
23

score 0 · Answer 2 · answered May 03 '13 at 09:42

I ask the question because I thought the answer was an evidence. It appears not.

So I do some average time for several queries. Here is the result, ordered by time :

.count(:conditions => c, :limit => 1) > 0;
=> 0.042037

.count(:conditions => c) > 0
=> 0.037781

.count(:select => '1', :conditions => c) > 0
=> 0.035976

.count(:select => '1', :conditions => c, :limit => 1) > 0
=> 0.034157

.find(:first, :conditions => c).nil?
=> 0.000377

.find(:first, :select => '1', :conditions => c).nil?
( equivalent to .exists?(c) )
=> 0.000184

The last one is the most efficient. Thanks for the tip of :select => '1' !

Find if there is a line on table : count or first?

2 Answers2