Hello! First, I know similar questions have been asked a lot but most answers are downright painfully slow. I have a 10,000,000 documents in a collection and I’m having serious issues with speed when filtering.
I’ve been using mongoose paginate v2 and speed is not absurdly painful but it is slow, taking around 27s to return the documents filtered with pagination. Recently, I learned that aggregate().skip().limit() is lightning fast with unfiltered data:
const pageSize = +req.query.pagesize;
const currentPage = +req.query.currentpage;
let recordCount;
ServiceClass.find().count().then((count) =>{
recordCount = count;
ServiceClass.aggregate().skip(currentPage).limit(pageSize).exec().then((documents) => {
res.status(200).json({
message: msgGettingRecordsSuccess,
serviceClasses: documents,
count: recordCount,
});
})
.catch((error) => {
res.status(500).json({ message: msgGettingRecordsError });
});
}).catch((error) => {
res.status(500).json({ message: "Error obteniendo cantidad de registros" });
});
This function returns any page within 8 ms, extremely fast and this is amazing but whenever I start filtering the issues start. First, If i just filter like this:
ServiceClass.aggregate([
{ $match: { $or: [{ code: { $regex: regex } }, { description: { $regex: regex } }] } },
{ $skip: currentPage },
{ $limit: pageSize }
])
The function takes around 14s to get the data, an improvement of almost 50% over the mongoose paginate plugin, however, I need the total amount of records without the limit so I can’t use $count inside that aggregate as I will just get pageSize, therefor, I need to run another query before:
ServiceClass.aggregate([
{ $match: { $or: [{ code: { $regex: regex } }, { description: { $regex: regex } }] } },
{ $count: "count" }
])
It kills the performance as both queries add up and in both queries I end up getting all documents, taking about 34s to complete.
So the question is as the title says: How can I get the filtered results along with total count of documents before limit as fast as possible?
both fields -code and description- have an index.