I have larger documents I'm storing in a collection, so I'm working hard to avoid full document scans.
Using v5.0.17
The documents each have 2 numeric fields: Unit and StepNumber. I'm using the following to create the index:
dbCollection.EnsureIndex(x => new { x.Unit, x.StepNumber });
I've confirmed that the index exists in the System $indexes table:
"units" "UnitUnitStepNumberStepNumber" "{Unit:$.Unit,StepNumber:$.StepNumber}" false 15
However, when I view the query execution plan it is clearly not using this index:
EXPLAIN
SELECT $ FROM units
where Unit = 38
AND StepNumber >= 356
AND StepNumber <= 360
I have larger documents I'm storing in a collection, so I'm working hard to avoid full document scans. Using v5.0.17 The documents each have 2 numeric fields: Unit and StepNumber. I'm using the following to create the index:
dbCollection.EnsureIndex(x => new { x.Unit, x.StepNumber });
I've confirmed that the index exists in the System $indexes table: "units" "UnitUnitStepNumberStepNumber" "{Unit:$.Unit,StepNumber:$.StepNumber}" false 15
However, when I view the query execution plan it is clearly not using this index:
EXPLAIN SELECT $ FROM units where Unit = 38 AND StepNumber >= 356 AND StepNumber <= 360
Results - index: {"name":"Unit","expr":"$.Unit","order":1,"mode":"INDEX SEEK(Unit = 38)","cost":10}
Any ideas on why it would not be using the compound index?
@mbdavid - any thoughts?