Mongo's documentation on $or operator 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 effectively, if you want the query to be efficient, both of the attributes used in the $or condition should be indexed.

However, I'm not sure if this applies to "findOne" operations as well since I can't use Mongo's explain functionality for a findOne operation. It seems logical to me that if you only care about returning one document, you would check an indexed condition first, since you could bail after just finding one without needing to care about the non-indexed fields.

Example

Let's pretend in the below that "email" is indexed, but username is not. It's a contrived example, but bear with me.

db.users.findOne(
   {
     $or: [
            { email : '[email protected]' },
            { username: 'some-user' }
          ]
   }
)

Would the above use the email index, or would it do a full collection scan until it finds a document that matches the criteria?

I could not find anything documenting what would be expected here. Does someone know the answer?

CodePudding user response：

Well I feel a little silly - it turns out that the docs I found saying you can only run explain on "find" may have been just referring to when you're in MongoDB Compass.

I ran the below (userEmail not indexed)

this.userModel
        .findOne()
        .where({ $or: [{ _id: id }, { userEmail: email }] })
        .explain();

this.userModel
        .getModelInstance()
        .findOne()
        .where({ _id: id })
        .explain();

...and found that the first does do a COLLSCAN and the second's plan was IDHACK (basically it does a normal ID lookup).

When running performance tests, I can see that the first is about 4x slower in a collection with 20K documents (depends on where in the natural order the document you're finding is)