How to optimize indexes around $or queries

Working with Data
1

If I have a query that is matching on multiple $or fields, should there be an index for each of the $or conditionals? Since from my understanding, Mongo technically makes X queries where X is the number of $or conditionals

2

Hey @J_Phoebus ,

Welcome to the MongoDB community. I will try to answer your question as queries and indexing is my favourite part of MongoDB.

Lets say you are using different value of the same fields using $OR like shown below:

$or : [ 
   { a: "alphabet"},
   { a: "numbers"},
   { a: "special chars"}
]

You can have query re-written as:

a : { $in:["alphabet","numbers","special chars"] }

In the above case you need only column a to be indexed.

Lets say you want to query different columns of the same collection using $or like below:

$or : [ 
   { a: "alphabet"},
   { b: "numbers"},
   { c: "special chars"}
]

In the above case you need to have single key indexes on a, b and c on all the fields that is using in the query.

Thanks,
Darshan

1 Like