mongoid limit(1).first vs .first vs find_by performance?

first_vs_limit.markdown

.first vs .limit(1) performance?

Q:

Hi there, I noticed the following:

This query:

AvailabilityUpdate.where(hotel_id: "AT_INN_BINDERS").only(:timestamp).limit(1).entries.first.timestamp

is much faster than if .first is used:

AvailabilityUpdate.where(hotel_id: "AT_INN_BINDERS").only(:timestamp).first.timestamp

is this on purpose?

I noticed further that:

AvailabilityUpdate.where(hotel_id: "AT_INN_BINDERS").only(:timestamp).limit(1).first.timestamp

ignores the limit, and uses limit(-1) in the query which is much slower...

can anyone tell me why this is?

A:

Durran Jordan

For your first example, yes that is expected. The reason is that MongoDB does not guarantee any sort of order as far as documents being returned from the database goes, so in order for Mongoid to have predictable first/last methods it automatically adds an _id sort to the queries if no other sorting criteria already existed. With a limit(1).entries.first no such sorting was added and Mongo can simply just immediately give you one document back without having to sort by id.

That's gone back and forth over the life of Mongoid - I removed it once before and had #first just give you the first thing Mongo does, but I got so many bug reports and pings about first/last not giving back expected results I put it back in. But it probably wouldn't take me much convincing to pull it out again, remove the #last method, and make people sort explicitly. But that would have to be a 4.0 change.

As for the limit(1).first, it's the same deal - once #first gets called the sorting fun goes in.

limit(1).first vs .first vs find_by

100.times

relation_score = GxRelationScore.where(user_id: user_id_pair[0], relation_user_id: user_id_pair[1]).first

method three: total find relation_score 100, total time 25.3 average_time 0.253

relation_score = GxRelationScore.where(user_id: user_id_pair[0], relation_user_id: user_id_pair[1]).limit(1).first

method three: total find relation_score 100, total time 14.5 average_time 0.145

relation_score = GxRelationScore.find_by(user_id: user_id_pair[0], relation_user_id: user_id_pair[1])

method three: total find relation_score 100, total time 14.5 average_time 0.145

single relation_score case

jhjguxin@jhjguxin-desktop:~/GuanXi/guanxiserver/gxservice$ rails c
Loading development environment (Rails 3.2.2)
1.9.3p392 :001 > user_id_pair = ["415", "44924"] => ["415", "44924"] 
1.9.3p392 :002 > GxRelationScore.where(user_id: user_id_pair[0], relation_user_id: user_id_pair[1]).limit(1).first
  MOPED: 58.215.189.124:27017 COMMAND      database=admin command={:ismaster=>1} (2040.1635ms)
  MOPED: 58.215.189.124:27017 QUERY        database=gxservice_development collection=gx_relation_scores selector={"$query"=>{"user_id"=>"415", "relation_user_id"=>"44924"}, "$orderby"=>{:_id=>1}} flags=[:slave_ok] limit=-1 skip=0 fields=nil (59.9208ms)
 => nil 
1.9.3p392 :003 > GxRelationScore.where(user_id: user_id_pair[0], relation_user_id: user_id_pair[1]).limit(1).first
  MOPED: 58.215.189.124:27017 QUERY        database=gxservice_development collection=gx_relation_scores selector={"$query"=>{"user_id"=>"415", "relation_user_id"=>"44924"}, "$orderby"=>{:_id=>1}} flags=[:slave_ok] limit=-1 skip=0 fields=nil (10.2754ms)
 => nil 
1.9.3p392 :004 > reload!
Reloading...
 => true 
1.9.3p392 :005 > GxRelationScore.where(user_id: user_id_pair[0], relation_user_id: user_id_pair[1]).limit(1).first
  MOPED: 58.215.189.124:27017 QUERY        database=gxservice_development collection=gx_relation_scores selector={"$query"=>{"user_id"=>"415", "relation_user_id"=>"44924"}, "$orderby"=>{:_id=>1}} flags=[:slave_ok] limit=-1 skip=0 fields=nil (45.5766ms)
 => nil 
1.9.3p392 :006 > GxRelationScore.find_by(user_id: user_id_pair[0], relation_user_id: user_id_pair[1])
  MOPED: 58.215.189.124:27017 QUERY        database=gxservice_development collection=gx_relation_scores selector={"$query"=>{"user_id"=>"415", "relation_user_id"=>"44924"}, "$orderby"=>{:_id=>1}} flags=[:slave_ok] limit=-1 skip=0 fields=nil (117.3742ms)

In mongoid 6 you now can do:

>>> myquery.first(id_sort: :none)

To avoid the sorting by ID.