Collection Schema
{
"_id" : ObjectId("5d3562bf1b48d90ea4b06a74"),
"name" : "19",
"location" : {
"type" : "Point",
"coordinates" : [
50.0480208,
30.5239127
]
}
}
Indexes
> db.places.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "test.places"
},
{
"v" : 2,
"key" : {
"location" : "2dsphere"
},
"name" : "location_2dsphere",
"ns" : "test.places",
"2dsphereIndexVersion" : 3
}
There is 2 milion documents is stored in collection.
First I ran query like this.
db.places.find({ location: {$geoWithin: { $center: [[60.0478308, 40.5237227], 10] } }})
But it takes 2 seconds. So I examine query via explain().
> db.places.find({ location: {$geoWithin: { $center: [[60.0478308, 40.5237227], 10] } }}).explain('executionStats')
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.places",
"indexFilterSet" : false,
"parsedQuery" : {
"location" : {
"$geoWithin" : {
"$center" : [
[
60.0478308,
40.5237227
],
10
]
}
}
},
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"location" : {
"$geoWithin" : {
"$center" : [
[
60.0478308,
40.5237227
],
10
]
}
}
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1414213,
"executionTimeMillis" : 2093,
"totalKeysExamined" : 0,
"totalDocsExamined" : 2000000,
"executionStages" : {
"stage" : "COLLSCAN",
"filter" : {
"location" : {
"$geoWithin" : {
"$center" : [
[
60.0478308,
40.5237227
],
10
]
}
}
},
"nReturned" : 1414213,
"executionTimeMillisEstimate" : 1893,
"works" : 2000002,
"advanced" : 1414213,
"needTime" : 585788,
"needYield" : 0,
"saveState" : 15681,
"restoreState" : 15681,
"isEOF" : 1,
"invalidates" : 0,
"direction" : "forward",
"docsExamined" : 2000000
}
},
"serverInfo" : {
"host" : "Johnui-iMac",
"port" : 27017,
"version" : "4.0.3",
"gitVersion" : "7ea530946fa7880364d88c8d8b6026bbc9ffa48c"
},
"ok" : 1
}
You know that query stage is COLLSCAN
.
I wonder that, I already created index for location
fields, but it seems doesnt' work.
So I create more indexes.
"v" : 2,
"key" : {
"location.coordinates" : 1
},
"name" : "location.coordinates_1",
"ns" : "test.places"
},
{
"v" : 2,
"key" : {
"location" : 1
},
"name" : "location_1",
"ns" : "test.places"
}
But it doesn't work too.
Is there any issue on my index configuration?
You seem to have created a 2dsphere
Index on your location, but the MongoDB docs on $centre
specify that:
Only the 2d geospatial index supports $center.
Therefore, I suggest you create a 2d
index on the location field and the scan will be performed using this index