const result = await strapi.query("model").find({
id: id,
_start: page > 0 ? (page - 1) * pageSize : 0,
_limit: pageSize,
_sort: "created_at:desc",
});
Let's say we have this strapi query and we use MongoDB. The initial query without pagination takes 15 seconds, would using pagination improve performances? I am thinking no, because MongoDB would still have to go through all the records. Is there any performance optimization done by MongoDB? If there's any, I am thinking it wouldn't matter if you perform joins during the aggregation process, because you still need to go through all the records, so performance gains would be minimal ~1 seconds.
1 Answer 1
There is not enough information here to diagnose the problem which therefore prevents us from commenting on whether or not pagination is an appropriate solution.
To better understand the problem, it may be best to gather .explain("executionStats") for the query as a first step. You can usually do so by taking the query of concern and appending the explain method to the end of it in the shell, see the documentation here. It may be the case that you are missing indexes (likely one on { id: 1, created_at: -1 } would help here). But there could be other factors such as resource bottlenecks, network latency, etc. The output from explain would help us narrow down the problem to better understand what solutions may be needed.
As an aside, is use of id intentional here? Note that all documents in MongoDB have a (mandatory) _id field.
There also appears to be some other potential misunderstandings with the remainder of the description in the question. To quickly touch on some of them:
because MongoDB would still have to go through all the records. Is there any performance optimization done by MongoDB?
Databases do not always have to go through the full set of matching results to return a subset of them. This is really only the case when it has to do something like sort the results and there isn't a matching index to help it do so. The index mentioned above would support the sort which should allow it to efficiently access and retrieve the requested subset of results.
There is some reference material on the topic here in their documentation.
I am thinking it wouldn't matter if you perform joins during the aggregation process
What joins are you referring to here?
In MongoDB, joins are handled via the $lookup aggregation stage. The sample query in the question doesn't seem to be an aggregation nor does it include a $lookup. Performance and questions about $lookup would likely be a different question.
Overall, it's certainly possible that this query can be improved from its current runtime of 15 seconds. Confirming that, and figuring out how to accomplish it, requires some additional information.