I have a MongoDB collection that has about 5 million documents. The data in the documents look something like the following:
{
since: [Some Unix timestamp ie. 1660106561)
team: ["a","b","c]
}
when I run the following query, my mongoDB connection times out:
db.myCollection.find({team: {$nin: ['b']}}).sort({since: -1})
I have the following compound indices on my collection:
[since, team]
Is there anything I can do to prevent this query from timing out?
This query is just very hard to execute, $nin is a none selective query, This means it does not utilize indexes well.
For instance, the inequality operators $nin and $ne are not very selective since they often match a large portion of the index. As a result, in many cases, a $nin or $ne query with an index may perform no better than a $nin or $ne query that must scan all documents in a collection.
Specfically in this context i believe the index is even hurting the query performance, because the index is a multikey index using $nin on it still forces scanning large portions of the index tree, after that the query engine still has to fetch and filter each document to make sure it does not have the B value. performance can get even worse if this is just a toy example and the carnality of values is even greater. essentially forcing the engine to do double work.
I reckon that if you execute this query without any index usage, are alternatively if the "since" date matches the "narutal" order and you can drop the skip, you can get much better performance like this:
db.collection.find({team: {$nin: ['b']}}).sort({$natural: -1}).hint({$natural: 1});
Mind you, any query that needs to scan millions of documents will not have a lightning fast execution speed.