Why is MongoDb not taking an existing, unique compound index for an $or query?

[Andreas_West]

I have a collection with almost 2.4 million docs (and growing strongly).

I have the following unique, compound index for the fields kit1 and kit2:

When I run a simple $or query where I check for both the kit1 and kit2 field I was expecting that it would use the index. But the explain feature tells me that there’s no index available for this query:

While Atlas is still fast (50 ms for the COLLSCAN), I want this query to use an index.

Question 1:
Why doesn’t it use the existing index?

Question 2:
What index do I need to add or how can I change my query to let it use the existing index?

Thanks in advance!

[Prasad_Saya]

Hello @Andreas_West, the reason is this: $or Clauses and Indexes. It says:

When evaluating the clauses in the $or expression, MongoDB either performs a collection scan or, if all the clauses are supported by indexes, MongoDB performs index scans. That is, for MongoDB to use indexes to evaluate an $or expression, all the clauses in the $or expression must be supported by indexes. Otherwise, MongoDB will perform a collection scan.

So, for the query to perform an index scan you need to have indexes on both the fields - individually.

[Andreas_West]

Thanks @Prasad_Saya. Is it because the index is an unique one?

I thought that it would be redundant to have the following 3 indexes:

kit1, kit2 as compound index
kit1
kit2

My (rather simplistic) thinking was that it could take the 1st index for all queries that require either the kit1 or kit2 or kit1 & kit2 field.

But reading your answer and seeing the explain I was obviously wrong.

[Prasad_Saya]

@Andreas_West

Is it because the index is an unique one?

Nope.

The query needs indexes on the fields on both side of the or. That is if your query filter is:

(kit1 == "x" OR kit2 == "y" )

then, there should be indexes on kit1, kit2, which would be used individually. So, if you have the following three indexes:

kit1, kit2 as compound index
kit1
kit2

I think you can keep the first compound index (kit1+kit2) and the index on kit2 (and drop the index on kit1). Another option is drop the compound index (in case you don’t have use of it elsewhere) and retain the two individual single field indexes on kit1 and kit2.

See this note about Compound Index Prefixes to understand why an index on kit1+kit2 can be used in lieu of an index on kit1 only.