Optimizing quey performance when using operators like $in $ne

[Gaurab_Panthee1 (Gaurab Panthee)]

Hi, In the Optimizing CRUD Operations, we learned that we should create indexes based on equality, sort and range.

In my current scenario i have a find operation similar to

find({user_id: {$in: [x,y,z]}, role: ${ne: “admin”}, <other multiple similar $ne operations>})

the total documents returned is 1570 but the index keys examined and documents examined are 3599 and 3525 resp. This is twice the number of search.

I tried creating compound index user_id, role, <other fields> but didn’t see any similar improvement over just using the index user_id.

How can i improve performance of such queries? And why is it searching for this many documents.

[Pavel_Duchovny]

Hi @Gaurab_Panthee1 ,

The $ne operators are considered non-selective operators, therefore you should not expect indexes to work well with them.

Instead of querying what should not be there you should query what do you want to fetch. For example if users are not admins set they role to “non-admin” and search for “non-admin”.

If this is not possible than perhaps try to use index only on user-id.

Thanks
Pavel