I have recently updated Spring Data MongoDB to 3.2.1 and the implementation of the count() method in MongoOperations seems to have changed. It uses an aggregation pipeline and does the counting in the group stage.
The aggregation pipeline looks as follows:
[
{
"$match": {}
},
{
"$group": {
"_id": 1,
"n": {
"$sum": 1
}
}
}
]
MongoDB Atlas is giving me alerts for each call, as the collection has over 250.000 entries and the Scanned Objects / Returned ratio is > 250.000 for each call. Scanned Objects / Returned ratio > 1000 is considered inefficient by Atlas by default, as it might be caused by a bad query/missing index. After doing some research, I found this GitHub issue https://github.com/spring-projects/spring-data-mongodb/issues/3522, which suggests to use estimatedCount() for empty queries. However, this does not solve the issue for queries that are not empty, but would also return a couple 1000 entries. If I create an index for those queries, it does not go through the documents, but through the index, yet the Examined:Returned in the MongoDB Atlas Profiler, which says "Only slow operations will be shown.", is still very high and I'm afraid that as the collection grows, so will the time needed for the query.
I have considered the following solutions:
- Prevent searches with > 1000 results and ask users to narrow down search
- Limit search to 1000 results (also limit the query for count) and tell users to narrow down search, when last page is reached
- Force each count to use an index (for example sort by "_id"), which at least should prevent the alerts
Nevertheless, I'm not happy with either of those. Is there a good solution that I've not thought about, but could help me solve this issue?