Query using index still taking time when using $in attribute and sorting

Working with Data
,
1

Hi all,
I have created indexes in the format {a: 1, b: 1, c: 1, d:1, e:1} on collection which has 11.1 million documents.
And the base query which I am applying is like
{a: true, b: ‘text’, c: {"$in": [long array]}, d: {"$in": [‘A’, 'B]}}.sort(e: 1)

So the query is using this index which is mentioned above, but the query is still taking 13 sec, and its returning 72 K records.
If I reduce the values for the ‘c’ attribute to a smaller array eg: [1,2] then the results are returned quicker.
But as soon as the array size is grown the time taken s increasing.
So what should be done in this case?
What I observed is if there is no sorting then the query is running faster but if I apply sorting the time is increasing and ‘Sorted in Memory’ is marked as yes. What type of index I should create so that there is no in-memory sort?
Thanks for the help in Advance!

2

Hi @Viraj_Chheda ,

You found exactly the reason why the query is slow and it is because of the fileds order which do not comply with the Equality Sort Range order.

A $in with a large array of inputs is actually a range query as it build different bounds to the plan.

The actual good order for the query is { a: 1, b: 1, d:1, e: 1, c: 1}

Having very large $in is also unadvisable and perhaps you can break the query into smaller batches.

Thanks
Pavel

2 Likes
3

hi @Pavel_Duchovny , Thanks for the suggestion of ESR order for indexing, that has worked for me.
For all the columns on which range i.e $in was getting applied, I have moved it at the end in indexing order.

1 Like
4

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.