Minor - P4 I have this query:
var cursor = groupCollection .Find( teamsQueryBuilder.ElemMatch(g => g.Teams, teamQueryBuilder.Eq(t => t.TeamId, teamId)) ); cursor.Options.Modifiers = new BsonDocument("$explain", true); var result = cursor.Project(new BsonDocument()).FirstOrDefault();
So, I created an index like this:
groupCollection.Indexes
.CreateOne(Builders<Group>.IndexKeys
.Ascending(g => g.Teams[-1].TeamId));
{
"v" : 1,
"name" : "teams.$.teamId_1",
"key" : {
"teams.$.teamId" : 1
},
"ns" : "spochdb_dev.organization.teamgroups"
}
When I ran the query, I got the following explain:
{
"cursor" : "BasicCursor",
"isMultiKey" : false,
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 1,
"nscannedObjectsAllPlans" : 1,
"nscannedAllPlans" : 1,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {},
"allPlans" : [{
"cursor" : "BasicCursor",
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 1,
"indexBounds" : {}
}
],
"server" : "DESKTOP-J8M6V6N:27017"
}
But, if I change the index creation code to this:
groupCollection.Indexes
.CreateOne(Builders<Group>.IndexKeys
.Ascending("teams.teamId"));
{
"v" : 1,
"name" : "teams.teamId_1",
"key" : {
"teams.teamId" : 1
},
"ns" : "spochdb_dev.organization.teamgroups"
}
and run the query again, I get the following explain:
{ {
"cursor" : "BtreeCursor teams.teamId_1",
"isMultiKey" : true,
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 1,
"nscannedObjectsAllPlans" : 1,
"nscannedAllPlans" : 1,
"scanAndOrder" : false,
"indexOnly" : false,
"nYields" : 0,
"nChunkSkips" : 0,
"millis" : 0,
"indexBounds" : {
"teams.teamId" : [[ObjectId("5881760396839d0e94fdf5a5"), ObjectId("5881760396839d0e94fdf5a5")]]
},
"allPlans" : [{
"cursor" : "BtreeCursor teams.teamId_1",
"n" : 1,
"nscannedObjects" : 1,
"nscanned" : 1,
"indexBounds" : {
"teams.teamId" : [[ObjectId("5881760396839d0e94fdf5a5"), ObjectId("5881760396839d0e94fdf5a5")]]
}
}
],
"oldPlan" : {
"cursor" : "BtreeCursor teams.teamId_1",
"indexBounds" : {
"teams.teamId" : [[ObjectId("5881760396839d0e94fdf5a5"), ObjectId("5881760396839d0e94fdf5a5")]]
}
},
"server" : "DESKTOP-J8M6V6N:27017"
}
}
We're using databse version v2.4.14 (git version 05bebf9ab15511a71bfbded684bb226014c0a553) at the moment, if that makes a difference.
If it doesn't make a difference, why is the driver generating unusable indexes?