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:
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.
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.