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

Question

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?

It is just a naming convention to use all lower case for collection names in mogoDB. use "example" instead of "example" https://stackoverflow.com/questions/9868323/is-there-a-convention-to-name-collection-in-mongodb/9868505 — Player_Neo, Jan 01 '19 at 08:36

Player_Neo · Accepted Answer · 2019-01-01T17:49:34.050

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

The problem the user will request for all rows with user defined pagination size. It is not just search for single name. How to sort based on name column and paginate the results. We cannot use gt or ls for name field. We advise. — Java Developer, Jan 01 '19 at 15:20
I mean we cannot go for approach #2 for name field eventhough performance is very good in approach #2 and confused how we can achieve it for other fields also similar to id field — Java Developer, Jan 01 '19 at 15:21
@bhau hoysala...as per the above link if we use skip and limit for user, it will affect the performance. is that right? no we don't have any other way other than using skip and limit when the column is other than id column...the link says it should be a serious performance issue if we use skip and limit. Please advise — Java Developer, Jan 01 '19 at 17:58
@JavaDeveloper I don't think so any other way is possible to achieve pagination without using limit() and skip() using rich query. when we add index on the fields used with find, sort, limit & skip functions it helps to enhance the performance to some good extent. — Player_Neo, Jan 01 '19 at 18:06

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

1 Answers1

For basic understand of Indexing in MonogDB