I have larger documents I'm storing in a collection, so I'm working hard to avoid full document scans. 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?
Steps:
- Create compound index on fields Unit & StepNumber
- Execute query with where criteria on both fields
- Examine the query execution plan
- Expected the new index to be used by the query
- Query plan shows the query using another index with only one of the fields