LiteDB v15.0.17 - Compound index is not being used by query

24 Views Asked by At

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:

  1. Create compound index on fields Unit & StepNumber
  2. Execute query with where criteria on both fields
  3. Examine the query execution plan
  4. Expected the new index to be used by the query
  5. Query plan shows the query using another index with only one of the fields
0

There are 0 best solutions below