Mongo Performance Improve

I have a collection of 5.5M records. I have an index, on a single field ‘description’. 2M of those records have description value as ‘First Lot’ and the rest 2M have them as ‘Second Lot’. After doing explain ExecutionStats, for queries on COUNT for ({“description” : “First Lot”}) and ({“description” : “Second Lot”}) the “executionTimeMillis” is 500-600 range for both but when I do COUNT for ({“description” : { $in : [“First Lot”, “Second Lot”]}}) the “executionTimeMillis” is minimum in the range of 2000. why is that? shouldn’t it be in the range of 1100-1200? How can I can reduce the time ??

Hi @KR_1 ,

Can you provide an explain plan with execution stats of both queries ?

In general having a value that return 2M documents out of 5M is not considered selective , and the $in operator might need to do a full index scan.

You might consider running 2 queries, 1- ({“description” : “First Lot”} and 2- ({“description” : “Second Lot”}), finally sum the numbers on client side…

Thanks
Pavel

1 Like