How to index and sorting with Pagination using custom field in MongoDB ex: name instead of id

user_10393198 0
Viewed 1336

https://scalegrid.io/blog/fast-paging-with-mongodb/

Example :  {
    _id,
    name,
    company,
    state
}

I've gone through the 2 scenarios explained in the above link and it says sorting by object id makes good performance while retrieve and sort the results. Instead of default sorting using object id , I want to index for my own custom field "name" and "company" want to sort and pagination on this two fields (Both fields holds the string value).

I am not sure how we can use gt or lt for a name, currently blocked on how to resolve this to provide pagination when a user sort by name.

How to index and do pagination for two fields?

mongodb
1 Answers

Answer to your question is 

db.Example.createIndex( { name: 1, company: 1 } )

And for pagination explanation the link you have shared on your question is good enough. Ex

db.Example.find({name = "John", country = "Ireland"}). limit(10);

For Sorting

db.Example.find().sort({"name" = 1, "country" = 1}).limit(userPassedLowerLimit).skip(userPassedUpperLimit);

If the user request to fetch 21-30 first documents after sorting on Name then country both in ascending order

db.Example.find().sort({"name" = 1, "country" = 1}).limit(30).skip(20);

For basic understand of Indexing in MonogDB

Indexes support the efficient execution of queries in MongoDB. Without indexes, MongoDB must perform a collection scan, i.e. scan every document in a collection, to select those documents that match the query statement. If an appropriate index exists for a query, MongoDB can use the index to limit the number of documents it must inspect.

Indexes are special data structures, that store a small portion of the collection’s data set in an easy to traverse form. The index stores the value of a specific field or set of fields, ordered by the value of the field.

Default _id Index

MongoDB creates a unique index on the _id field during the creation of a collection. The _id index prevents clients from inserting two documents with the same value for the _id field. You cannot drop this index on the _id field.

Create an Index

Syntax to execute on Mongo Shell

db.collection.createIndex( <key and index type specification>, <options> )

Ex:

db.collection.createIndex( { name: -1 } )

for ascending use 1,for descending use -1

The above rich query only creates an index if an index of the same specification does not already exist.

Index Types

MongoDB provides different index types to support specific types of data and queries. But i would like to mention 2 important types

1. Single Field In addition to the MongoDB-defined _id index, MongoDB supports the creation of user-defined ascending/descending indexes on a single field of a document.

2. Compound Index MongoDB also supports user-defined indexes on multiple fields, i.e. compound indexes.

The order of fields listed in a compound index has significance. For instance, if a compound index consists of { name: 1, company: 1 }, the index sorts first by name and then, within each name value, sorts by company.

Source for my understanding and answer and to know more about MongoDB indexing MongoDB Indexing

user_21535760
Related