I have a pretty simple query in Atlas which seems to be largely ignoring my defined index. I’m confident that I’m ignorant of some detail of how the index should work. However when I run what looks to me to be the same query in compass with explain the index is used.
The collection is called cars and has a few fields on it we’re interested in. The first is the deletedAt field a date and the second userIds an array of ObjectIds. The query I’m running is
I didn’t see anything in the plan cache that would explain this behaviour and I added that index quite a while back so I assume that somewhere along the line that plan would have been recalculated. I’m afraid I’m not very familiar with mongo so chances are this is something simple. Please feel free to just point me at the appropriate docs.
Hi @Simon_Timms and welcome in the MongoDB Community !
From what I see, you are running a different query in Atlas because you have also a sort and a projection. And because it says “In Memory Sort” = “No”, I have to imagine it’s not using the userIds index but rather a createdAt index which probably also exists.
So in the second query with the sort and the projection, MongoDB chooses to avoid the in memory sort. It could use the other index though and it would be more efficient - but it cannot guess that.
A better index for your query though would be:
{"userIds": 1, "deletedAt": 1, "createdAt": 1}
With this compound index, you have a nice selectivity (very little keys needs to be scanned if you don’t have the same objectId in many documents) and also the sort is included so no inefficient in memory sort.
That’s interesting. There is indeed a createdAt index which is perhaps not the optimal index to use. I added the suggested index and I’ll see if that improves things. For my future reference is there a way to provide index utilization hints to the engine? It seems like in this case it selected a highly sub-optimal index especially considering that I wasn’t even filtering by the created date but only using it in sorting.
Yes, you can hint but in 99.9% of the cases, that’s usually a bad idea.
In your present case, an in-memory sort is always bad compared to a free sort (which you get because the index is already sorted and you are retrieving the docs in the same order). So in your case, MongoDB had to choose between a bad index and a poor index. It chose the best it could use, but the correct solution here is not to hint, it’s to create a good index. The one I proposed was definitely better than the 2 others.
Please make sure to re-run your query with an explain(true) to double check that your query now executes efficiently (== not scanning a lot of entries in the index and no in-memory sort).
!
.