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.
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
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:
To avoid the sorting by ID.