How to index and sorting with Pagination using custom field in MongoDB ex: name instead of id
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
add a comment |
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
It is just a naming convention to use all lower case for collection names in mogoDB. use "example" instead of "example" stackoverflow.com/questions/9868323/…
– Bhanu Hoysala
Jan 1 at 8:36
add a comment |
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
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
mongodb
edited Jan 1 at 8:54


James Z
11.2k71935
11.2k71935
asked Jan 1 at 7:24


Java DeveloperJava Developer
132
132
It is just a naming convention to use all lower case for collection names in mogoDB. use "example" instead of "example" stackoverflow.com/questions/9868323/…
– Bhanu Hoysala
Jan 1 at 8:36
add a comment |
It is just a naming convention to use all lower case for collection names in mogoDB. use "example" instead of "example" stackoverflow.com/questions/9868323/…
– Bhanu Hoysala
Jan 1 at 8:36
It is just a naming convention to use all lower case for collection names in mogoDB. use "example" instead of "example" stackoverflow.com/questions/9868323/…
– Bhanu Hoysala
Jan 1 at 8:36
It is just a naming convention to use all lower case for collection names in mogoDB. use "example" instead of "example" stackoverflow.com/questions/9868323/…
– Bhanu Hoysala
Jan 1 at 8:36
add a comment |
1 Answer
1
active
oldest
votes
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 1 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 1 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 1 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.
– Bhanu Hoysala
Jan 1 at 18:06
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53993747%2fhow-to-index-and-sorting-with-pagination-using-custom-field-in-mongodb-ex-name%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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 1 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 1 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 1 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.
– Bhanu Hoysala
Jan 1 at 18:06
add a comment |
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 1 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 1 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 1 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.
– Bhanu Hoysala
Jan 1 at 18:06
add a comment |
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
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
edited Jan 1 at 17:49
answered Jan 1 at 8:16


Bhanu HoysalaBhanu Hoysala
3541312
3541312
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 1 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 1 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 1 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.
– Bhanu Hoysala
Jan 1 at 18:06
add a comment |
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 1 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 1 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 1 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.
– Bhanu Hoysala
Jan 1 at 18:06
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 1 at 15:20
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 1 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 1 at 15:21
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 1 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 1 at 17:58
@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 1 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.
– Bhanu Hoysala
Jan 1 at 18:06
@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.
– Bhanu Hoysala
Jan 1 at 18:06
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53993747%2fhow-to-index-and-sorting-with-pagination-using-custom-field-in-mongodb-ex-name%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
It is just a naming convention to use all lower case for collection names in mogoDB. use "example" instead of "example" stackoverflow.com/questions/9868323/…
– Bhanu Hoysala
Jan 1 at 8:36