Sorting (sort) and pagination (limit, skip)

In MongoDB, sorting (sort) and pagination (limit, skip) are core operations for data processing. Sorting controls the order of returned results, while pagination helps efficiently handle large datasets by avoiding loading excessive data at once. Combining these two operations can significantly improve query performance and user experience.

Basic Usage of Sorting (sort)

The sort method is used to order query results. You can specify one or more fields and control ascending (1) or descending (-1) order. For example, sorting a user collection by age in ascending order:

db.users.find().sort({ age: 1 });

If sorting by multiple fields is needed, such as first by age in ascending order and then by name in descending order:

db.users.find().sort({ age: 1, name: -1 });

Relationship Between Sorting and Indexes

If there is an index on the sorting field, MongoDB can leverage the index to directly return ordered results, avoiding a full collection scan. For example, after creating an index on the age field, sorting performance improves significantly:

db.users.createIndex({ age: 1 });

However, note that if the sorting direction is opposite to the index direction (e.g., the index is { age: 1 } but sorting uses { age: -1 }), performance may degrade.

Pagination Operations (limit and skip)

Pagination is typically implemented using limit and skip. limit restricts the number of returned documents, while skip skips a specified number of documents. For example, fetching the second page of data (10 items per page):

db.users.find().skip(10).limit(10);

Performance Issues with skip

skip performs poorly with large datasets because it must traverse and discard preceding documents. For example, skip(10000) forces MongoDB to scan the first 10,000 documents. An alternative is to use range queries, such as recording the _id of the last document from the previous page:

// Assuming the _id of the last document on the previous page is "last_id"
db.users.find({ _id: { $gt: ObjectId("last_id") } }).limit(10);

Combining Sorting and Pagination

Sorting and pagination are often used together. For example, displaying users in pages sorted by registration time in descending order:

db.users.find()
  .sort({ registeredAt: -1 })
  .skip(20)
  .limit(10);

Practical Scenario Example

Suppose an e-commerce platform needs to display products in pages, sorted by price and sales volume:

// First page: sorted by price in ascending order, 5 items per page
db.products.find()
  .sort({ price: 1 })
  .limit(5);

// Second page: skip the first 5 items
db.products.find()
  .sort({ price: 1 })
  .skip(5)
  .limit(5);

Alternative Pagination Methods

For deep pagination (e.g., the 100th page), skip is highly inefficient. In such cases, cursor-based pagination can be used, for example:

// First page
const firstPage = db.products.find().sort({ _id: 1 }).limit(10);
const lastId = firstPage[firstPage.length - 1]._id;

// Second page: use the _id of the last document from the previous page as the starting point
db.products.find({ _id: { $gt: lastId } })
  .sort({ _id: 1 })
  .limit(10);

Sorting in Aggregation Pipelines

In aggregation pipelines, the $sort stage can be combined with other stages (e.g., $skip, $limit):

db.orders.aggregate([
  { $match: { status: "completed" } },
  { $sort: { total: -1 } }, // Sort by total amount in descending order
  { $skip: 30 },
  { $limit: 10 }
]);

Pagination and Performance Optimization

To optimize pagination performance, you can:

1. Avoid large offsets (e.g., skip(10000)).
2. Use covered queries (querying only indexed fields).
3. Combine with caching to reduce database load.

For example, pagination querying only indexed fields:

db.users.find({}, { name: 1, age: 1 })
  .sort({ age: 1 })
  .limit(10);