How to index and sort with pagination using custom field in MongoDB?

To implement index and sorting with pagination using custom fields in MongoDB, combine compound indexes with sort(), limit(), and skip() methods. This approach ensures efficient query performance for paginated results.

Syntax

db.collection.createIndex({"field1": 1, "field2": 1});
db.collection.find().sort({"field1": 1, "field2": 1}).limit(pageSize).skip(pageNumber * pageSize);

Create Sample Data

First, create a compound index on Name and CountryName fields for optimal sorting performance ?

db.demo373.createIndex({"Name": 1, "CountryName": 1});

{
    "createdCollectionAutomatically": true,
    "numIndexesBefore": 1,
    "numIndexesAfter": 2,
    "ok": 1
}

Insert sample documents ?

db.demo373.insertMany([
    {"Name": "Chris", "Age": 22, "CountryName": "US"},
    {"Name": "David", "Age": 21, "CountryName": "AUS"},
    {"Name": "Bob", "Age": 23, "CountryName": "UK"},
    {"Name": "John", "Age": 21, "CountryName": "US"},
    {"Name": "Carol", "Age": 23, "CountryName": "AUS"}
]);

View All Documents

db.demo373.find();

{ "_id": ObjectId("..."), "Name": "Chris", "Age": 22, "CountryName": "US" }
{ "_id": ObjectId("..."), "Name": "David", "Age": 21, "CountryName": "AUS" }
{ "_id": ObjectId("..."), "Name": "Bob", "Age": 23, "CountryName": "UK" }
{ "_id": ObjectId("..."), "Name": "John", "Age": 21, "CountryName": "US" }
{ "_id": ObjectId("..."), "Name": "Carol", "Age": 23, "CountryName": "AUS" }

Example: Pagination with Custom Field Sorting

Implement pagination by sorting on Name and Age, then skip 2 documents and limit to 4 results ?

db.demo373.find().sort({"Name": 1, "Age": 1}).limit(4).skip(2);

{ "_id": ObjectId("..."), "Name": "Chris", "Age": 22, "CountryName": "US" }
{ "_id": ObjectId("..."), "Name": "David", "Age": 21, "CountryName": "AUS" }
{ "_id": ObjectId("..."), "Name": "John", "Age": 21, "CountryName": "US" }

Key Points

• Compound indexes improve performance when sorting by multiple fields
• skip() determines how many documents to bypass
• limit() controls the maximum number of results returned
• Index field order should match your sort order for optimal performance

Conclusion

Effective pagination in MongoDB requires proper indexing on sort fields combined with skip() and limit() methods. Create compound indexes matching your sort criteria to ensure efficient query execution for large datasets.