how to fix Sort, limit and skip do not work on large amounts of data

Question

I am using pagination for mat-table and for that I have written mongoldb query as follows, but this query is not working for big data it is giving error as : Executor error during find command :: caused by :: Sort operation used more than the maximum 33554432 bytes of RAM. Add an index, or specify a smaller limit.

const sort = { orderId: -1 };
collection.find({ status: { $ne: "cancel" } }).sort(sort).limit(pageSize).skip(skips).toArray(function (err, response) {});

I am expecting output in defending order according to orderid.

Answer 1

In general, you need to add indexes on fields on which you want to sort. The error happens because there's no index on orderId that MongoDB could use and that means sorting the results in memory. That works for small amount of data but in this case it doesn't because there's a memory cap (32MB) on how much memory can be used during the operation. Try adding the following index:

db.my_collection.createIndex({ status: 1, orderId: -1 }, { background: true })

This should help you to both search based on status and with the sort. Notice that the index will be used effectively even though the sort is based on a non-prefix subset. That's because the first part of the query includes an equality comparison on the first key. More at Use Indexes to Sort Query Results.