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












2















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?










share|improve this question

























  • 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
















2















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?










share|improve this question

























  • 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














2












2








2


1






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?










share|improve this 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?







mongodb






share|improve this question















share|improve this question













share|improve this question




share|improve this question








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



















  • 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












1 Answer
1






active

oldest

votes


















0














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






share|improve this answer


























  • 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











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
});


}
});














draft saved

draft discarded


















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









0














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






share|improve this answer


























  • 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
















0














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






share|improve this answer


























  • 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














0












0








0







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






share|improve this answer















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







share|improve this answer














share|improve this answer



share|improve this answer








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



















  • 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




















draft saved

draft discarded




















































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.




draft saved


draft discarded














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





















































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







Popular posts from this blog

MongoDB - Not Authorized To Execute Command

How to fix TextFormField cause rebuild widget in Flutter

in spring boot 2.1 many test slices are not allowed anymore due to multiple @BootstrapWith