Description
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?