I tried this query
db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.053980478460939611 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { "$all" : [/^warung/, /^nasi/] } }).skip(20).limit(20);
This is what the log from Amazon EC2 instance micro says
Fri Sep 07 03:21:08 [clientcursormon] mem (MB) res:312 virt:12424 mapped:6094
Fri Sep 07 03:21:43 [conn52] query isikotacobacoba.tablebusiness query: { $query: { LongitudeLatitude: { $nearSphere: [ 106.772835, -6.186753 ], $maxDistance: 0.05398047846093961 }, Prominent: { $gte: 15 }, indexContents: { $all: [ /^warung/, /^nasi/ ] } }, $hint: { LongitudeLatitude: "2d", Prominent: -1, indexContents: 1 } } ntoreturn:20 ntoskip:20 nscanned:40 nreturned:20 reslen:1141 567133ms
Fri Sep 07 03:22:04 [DataFileSync] flushing mmap took 15ms for 9 files
If I use my own local computer with 8GB memory the result is fast, namely 2 seconds. However, if I do not limit the query the result is still slow. For example:
db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.053980478460939611 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { "$all" : [/^warung/, /^nasi/] } }).limit(200);
Takes a VERY long time. Now, finding closest 200 points are not supposed to be tough right?
So memory couldn't possibly be the issue. How come trying to find 200 points take a long time if there are only 3600 points within 5 km.
Here is the log on a large 8GB i5 machine
Fri Sep 07 12:29:23 [conn5] command admin.$cmd command: { buildinfo: 1 } ntoreturn:1 reslen:340 0ms
Fri Sep 07 12:29:25 [conn4] query isikotacobacoba.tablebusiness query: { LongitudeLatitude: { $nearSphere: [ 106.772835, -6.186753 ], $maxDistance: 0.05398047846093961 }, Prominent: { $gte: 15 }, indexContents: { $all: [ /^warung/, /^nasi/ ] } } ntoreturn:100000 ntoskip:20 nscanned:262 nreturned:242 reslen:300329 501562ms
Fri Sep 07 12:29:34 [conn4] run command admin.$cmd { ping: 1 }
This is samples of typical data
{
"_id" : "warung-nasi-nur-karomah__-6.19_106.78",
"BuildingID" : null,
"Title" : "Warung Nasi Nur Karomah",
"InBuildingAddress" : null,
"Building" : null,
"Street" : "Jl. Arjuna Utara No.35",
"Districts" : [],
"City" : "Jakarta",
"Country" : "Indonesia",
"Checkin" : 0,
"Note" : null,
"PeopleCount" : 0,
"Prominent" : 45.5,
"CountViews" : 0,
"StreetAdditional" : null,
"LongitudeLatitude" : {
"Longitude" : 106.775693893433,
"Latitude" : -6.18759540055471
},
"Rating" : {
"Stars" : 0.0,
"Weight" : 0.0
},
"CurrentlyWorkedURL" : null,
"Reviews" : [],
"ZIP" : null,
"Tags" : ["Restaurant"],
"Phones" : ["081380087011"],
"Website" : null,
"Email" : null,
"Price" : null,
"openingHour" : null,
"Promotions" : [],
"SomethingWrong" : false,
"BizMenus" : [],
"Brochures" : [],
"Aliases" : [],
"indexContents" : ["restaura", "estauran", "staurant", "taurant", "aurant", "urant", "rant", "ant", "nt", "t", "warung", "arung", "rung", "ung", "ng", "g", "nasi", "asi", "si", "i", "nur", "ur", "r", "karomah", "aromah", "romah", "omah", "mah", "ah", "h"]
}
This is the log of the same query on my home machine (not amazon ec2 instance micro)
Fri Sep 07 10:52:28 [conn1] query isikotacobacoba.tablebusiness query: { LongitudeLatitude: { $nearSphere: [ 106.772835, -6.186753 ], $maxDistance: 0.05398047846093961 }, Prominent: { $gte: 15 }, indexContents: { $all: [ /^warung/, /^nasi/ ] } } ntoreturn:50 nscanned:50 nreturned:50 reslen:62090 2048ms
I understand that amazonec2 is slower than my home computer
The index is
db.tablebusiness.getIndexes();
[
{
"v" : 1,
"key" : {
"_id" : 1
},
"ns" : "isikotacobacoba.tablebusiness",
"name" : "_id_"
},
{
"v" : 1,
"key" : {
"LongitudeLatitude" : "2d",
"Prominent" : -1,
"indexContents" : 1
},
"ns" : "isikotacobacoba.tablebusiness",
"name" : "LongLat_Prominent_indexContents",
"dropDups" : false,
"background" : false
},
{
"v" : 1,
"key" : {
"LongitudeLatitude" : "2d",
"Prominent" : -1
},
"ns" : "isikotacobacoba.tablebusiness",
"name" : "LongLat_Prominent",
"dropDups" : false,
"background" : false
}
]
As you see it is properly indexes
One possible issue is lack of memory in amazon micro instance.
However, the nearSphere is limited by 0.053980478460939611 degree (around 5 km). Even without indexes, even with just table scan, it shouldn't need that much memory.
What's the real problem?
Here is the buildinfo of the mongodb
> db.runCommand("buildInfo")
{
"version" : "2.0.7",
"gitVersion" : "875033920e8869d284f32119413543fa475227bf",
"sysInfo" : "windows sys.getwindowsversion(major=6, minor=1, build=7601,
platform=2, service_pack='Service Pack 1') BOOST_LIB_VERSION=1_42",
"versionArray" : [
2,
0,
7,
0
],
"bits" : 64,
"debug" : false,
"maxBsonObjectSize" : 16777216,
"ok" : 1
}
>
I did some further testing:
db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.053980478460939611 } }).skip(20).limit(100000);
Returns "only" 3600 documents. Actually it does take 500 seconds.
Even if mongodb doesn't use index, scanning 3600 documents, computing the distance and then sort them shouldn't take long even for a micro machine.
Now, if I don't use $nearsphere but $near instead things are better but still dissapointing
Fri Sep 07 04:49:38 [conn61] query isikotacobacoba.tablebusiness query: { LongitudeLatitude: { $near: [ 106.772835, -6.186753 ], $maxDistance: 0.05398047846093961 }, Prominent: { $gte: 15.0 }, indexContents: { $all: [ /^warung/, /^nasi/ ] } } ntoreturn:20 ntoskip:20 nscanned:32 nreturned:12 reslen:14984 49636ms
Fri Sep 07 04:49:38 [conn61] run command admin.$cmd { replSetGetStatus: 1, forShell: 1 }
