Mixing
How does Indexing a field affect the sorting order of docs without the indexed field in MongoDB?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have a collection students with few docs
[{id:1, name:'AA'}, {id:2, name:'BB'}]
I am fetching docs sorted by a field which is not present in the docs
db.students.find().sort({marks: -1})
It gives me docs in this order
[{id:1, name:'AA'}, {id:2, name:'BB'}]
Now when I add an index
db.students.createIndex({'marks':1})
Then call the same query
db.students.find().sort({marks: -1})
The order was changed!!
[{id:2, name:'BB'},{id:1, name:'AA'}]
How does indexing a field affect the sorting order of documents which lack that field?
Note: This may not be a working example. But my issue is something similar.
mongodb indexing
edited Jan 4 at 5:28
Pynchia
6,75132132
asked Jan 3 at 12:04
Sreeragh A RSreeragh A R
1,2161332
add a comment |
I have a collection students with few docs
[{id:1, name:'AA'}, {id:2, name:'BB'}]
I am fetching docs sorted by a field which is not present in the docs
db.students.find().sort({marks: -1})
It gives me docs in this order
[{id:1, name:'AA'}, {id:2, name:'BB'}]
Now when I add an index
db.students.createIndex({'marks':1})
Then call the same query
db.students.find().sort({marks: -1})
The order was changed!!
[{id:2, name:'BB'},{id:1, name:'AA'}]
How does indexing a field affect the sorting order of documents which lack that field?
Note: This may not be a working example. But my issue is something similar.
mongodb indexing
edited Jan 4 at 5:28
Pynchia
6,75132132
asked Jan 3 at 12:04
Sreeragh A RSreeragh A R
1,2161332
add a comment |
I have a collection students with few docs
[{id:1, name:'AA'}, {id:2, name:'BB'}]
I am fetching docs sorted by a field which is not present in the docs
db.students.find().sort({marks: -1})
It gives me docs in this order
[{id:1, name:'AA'}, {id:2, name:'BB'}]
Now when I add an index
db.students.createIndex({'marks':1})
Then call the same query
db.students.find().sort({marks: -1})
The order was changed!!
[{id:2, name:'BB'},{id:1, name:'AA'}]
How does indexing a field affect the sorting order of documents which lack that field?
Note: This may not be a working example. But my issue is something similar.
mongodb indexing
edited Jan 4 at 5:28
Pynchia
6,75132132
asked Jan 3 at 12:04
Sreeragh A RSreeragh A R
1,2161332
I have a collection students with few docs
[{id:1, name:'AA'}, {id:2, name:'BB'}]
I am fetching docs sorted by a field which is not present in the docs
db.students.find().sort({marks: -1})
It gives me docs in this order
[{id:1, name:'AA'}, {id:2, name:'BB'}]
Now when I add an index
db.students.createIndex({'marks':1})
Then call the same query
db.students.find().sort({marks: -1})
The order was changed!!
[{id:2, name:'BB'},{id:1, name:'AA'}]
How does indexing a field affect the sorting order of documents which lack that field?
Note: This may not be a working example. But my issue is something similar.
mongodb indexing
mongodb indexing
edited Jan 4 at 5:28
Pynchia
6,75132132
asked Jan 3 at 12:04
Sreeragh A RSreeragh A R
1,2161332
edited Jan 4 at 5:28
Pynchia
6,75132132
asked Jan 3 at 12:04
Sreeragh A RSreeragh A R
1,2161332
edited Jan 4 at 5:28
Pynchia
6,75132132
edited Jan 4 at 5:28
Pynchia
6,75132132
edited Jan 4 at 5:28
Pynchia
6,75132132
6,75132132
asked Jan 3 at 12:04
Sreeragh A RSreeragh A R
1,2161332
asked Jan 3 at 12:04
Sreeragh A RSreeragh A R
1,2161332
asked Jan 3 at 12:04
Sreeragh A RSreeragh A R
1,2161332
1,2161332
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Mongodb will perform sorting in two different times.
- query time ( without index on that field)
- indexing time. ( when we create index)
for that reason you got different result for same query.
Without index:
when we perform sort ( without index on sort filed) in mongodb will start sorting collection in querying time by scanning entire collection.
The direction of the sort will be forward( even marks : -1) i.e it will first touches documents on their insertion order( _id value).
When it encounters two documents with same field( marks: null for two documents), then it will arrange them with their _id value.
With index:
you created index on marks field in ascending order( Indexing is nothing but generating B-tree with key as marks).
when we try to sort the collection with marks:1 then we will get same results in indexed order.
when we try to sort the collection with marks:-1 then mongodb will start returning documents from backwards because documents already indexed (sorted) in ascending order.
That's why we got different results.
You can get more details on these queries when you perform explain() on them.
Without Index:
db.students2.find().sort({marks:-1}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "stackoverflow.students2",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [ ]
},
"winningPlan" : {
"stage" : "SORT",
"sortPattern" : {
"marks" : -1
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "COLLSCAN",
"filter" : {
"$and" : [ ]
},
"direction" : "forward"
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "sys2030",
"port" : 27017,
"version" : "3.2.22",
"gitVersion" : "105adca0d443f9a1a5abd608fd7133840a68dd"
},
"ok" : 1
}
We don't have index so mongodb starts scanning it on query time.
With Index:
db.students.find().sort({marks:-1}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "stackoverflow.students",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [ ]
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"marks" : 1
},
"indexName" : "marks_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"marks" : [
"[MaxKey, MinKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "sys2030",
"port" : 27017,
"version" : "3.2.22",
"gitVersion" : "105adca0d443f9a1a5abd608fd7133840a68dd"
},
"ok" : 1
}
We have index , so mongodb starts using it and simply returns the results from backwards(marks:-1).
This is the reason when we perform sort when collection is large, monogodb will give error as: sort with large amount of data , without index.
Further Reading:
https://docs.mongodb.com/manual/tutorial/sort-results-with-indexes/
https://docs.mlab.com/indexing/
answered Jan 4 at 5:16
BhuvanachanduBhuvanachandu
957
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%2f54021955%2fhow-does-indexing-a-field-affect-the-sorting-order-of-docs-without-the-indexed-f%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
Mongodb will perform sorting in two different times.
- query time ( without index on that field)
- indexing time. ( when we create index)
for that reason you got different result for same query.
Without index:
when we perform sort ( without index on sort filed) in mongodb will start sorting collection in querying time by scanning entire collection.
The direction of the sort will be forward( even marks : -1) i.e it will first touches documents on their insertion order( _id value).
When it encounters two documents with same field( marks: null for two documents), then it will arrange them with their _id value.
With index:
you created index on marks field in ascending order( Indexing is nothing but generating B-tree with key as marks).
when we try to sort the collection with marks:1 then we will get same results in indexed order.
when we try to sort the collection with marks:-1 then mongodb will start returning documents from backwards because documents already indexed (sorted) in ascending order.
That's why we got different results.
You can get more details on these queries when you perform explain() on them.
Without Index:
db.students2.find().sort({marks:-1}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "stackoverflow.students2",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [ ]
},
"winningPlan" : {
"stage" : "SORT",
"sortPattern" : {
"marks" : -1
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "COLLSCAN",
"filter" : {
"$and" : [ ]
},
"direction" : "forward"
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "sys2030",
"port" : 27017,
"version" : "3.2.22",
"gitVersion" : "105adca0d443f9a1a5abd608fd7133840a68dd"
},
"ok" : 1
}
We don't have index so mongodb starts scanning it on query time.
With Index:
db.students.find().sort({marks:-1}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "stackoverflow.students",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [ ]
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"marks" : 1
},
"indexName" : "marks_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"marks" : [
"[MaxKey, MinKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "sys2030",
"port" : 27017,
"version" : "3.2.22",
"gitVersion" : "105adca0d443f9a1a5abd608fd7133840a68dd"
},
"ok" : 1
}
We have index , so mongodb starts using it and simply returns the results from backwards(marks:-1).
This is the reason when we perform sort when collection is large, monogodb will give error as: sort with large amount of data , without index.
Further Reading:
https://docs.mongodb.com/manual/tutorial/sort-results-with-indexes/
https://docs.mlab.com/indexing/
answered Jan 4 at 5:16
BhuvanachanduBhuvanachandu
957
add a comment |
Mongodb will perform sorting in two different times.
- query time ( without index on that field)
- indexing time. ( when we create index)
for that reason you got different result for same query.
Without index:
when we perform sort ( without index on sort filed) in mongodb will start sorting collection in querying time by scanning entire collection.
The direction of the sort will be forward( even marks : -1) i.e it will first touches documents on their insertion order( _id value).
When it encounters two documents with same field( marks: null for two documents), then it will arrange them with their _id value.
With index:
you created index on marks field in ascending order( Indexing is nothing but generating B-tree with key as marks).
when we try to sort the collection with marks:1 then we will get same results in indexed order.
when we try to sort the collection with marks:-1 then mongodb will start returning documents from backwards because documents already indexed (sorted) in ascending order.
That's why we got different results.
You can get more details on these queries when you perform explain() on them.
Without Index:
db.students2.find().sort({marks:-1}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "stackoverflow.students2",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [ ]
},
"winningPlan" : {
"stage" : "SORT",
"sortPattern" : {
"marks" : -1
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "COLLSCAN",
"filter" : {
"$and" : [ ]
},
"direction" : "forward"
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "sys2030",
"port" : 27017,
"version" : "3.2.22",
"gitVersion" : "105adca0d443f9a1a5abd608fd7133840a68dd"
},
"ok" : 1
}
We don't have index so mongodb starts scanning it on query time.
With Index:
db.students.find().sort({marks:-1}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "stackoverflow.students",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [ ]
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"marks" : 1
},
"indexName" : "marks_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"marks" : [
"[MaxKey, MinKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "sys2030",
"port" : 27017,
"version" : "3.2.22",
"gitVersion" : "105adca0d443f9a1a5abd608fd7133840a68dd"
},
"ok" : 1
}
We have index , so mongodb starts using it and simply returns the results from backwards(marks:-1).
This is the reason when we perform sort when collection is large, monogodb will give error as: sort with large amount of data , without index.
Further Reading:
https://docs.mongodb.com/manual/tutorial/sort-results-with-indexes/
https://docs.mlab.com/indexing/
answered Jan 4 at 5:16
BhuvanachanduBhuvanachandu
957
add a comment |
Mongodb will perform sorting in two different times.
- query time ( without index on that field)
- indexing time. ( when we create index)
for that reason you got different result for same query.
Without index:
when we perform sort ( without index on sort filed) in mongodb will start sorting collection in querying time by scanning entire collection.
The direction of the sort will be forward( even marks : -1) i.e it will first touches documents on their insertion order( _id value).
When it encounters two documents with same field( marks: null for two documents), then it will arrange them with their _id value.
With index:
you created index on marks field in ascending order( Indexing is nothing but generating B-tree with key as marks).
when we try to sort the collection with marks:1 then we will get same results in indexed order.
when we try to sort the collection with marks:-1 then mongodb will start returning documents from backwards because documents already indexed (sorted) in ascending order.
That's why we got different results.
You can get more details on these queries when you perform explain() on them.
Without Index:
db.students2.find().sort({marks:-1}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "stackoverflow.students2",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [ ]
},
"winningPlan" : {
"stage" : "SORT",
"sortPattern" : {
"marks" : -1
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "COLLSCAN",
"filter" : {
"$and" : [ ]
},
"direction" : "forward"
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "sys2030",
"port" : 27017,
"version" : "3.2.22",
"gitVersion" : "105adca0d443f9a1a5abd608fd7133840a68dd"
},
"ok" : 1
}
We don't have index so mongodb starts scanning it on query time.
With Index:
db.students.find().sort({marks:-1}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "stackoverflow.students",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [ ]
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"marks" : 1
},
"indexName" : "marks_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"marks" : [
"[MaxKey, MinKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "sys2030",
"port" : 27017,
"version" : "3.2.22",
"gitVersion" : "105adca0d443f9a1a5abd608fd7133840a68dd"
},
"ok" : 1
}
We have index , so mongodb starts using it and simply returns the results from backwards(marks:-1).
This is the reason when we perform sort when collection is large, monogodb will give error as: sort with large amount of data , without index.
Further Reading:
https://docs.mongodb.com/manual/tutorial/sort-results-with-indexes/
https://docs.mlab.com/indexing/
answered Jan 4 at 5:16
BhuvanachanduBhuvanachandu
957
Mongodb will perform sorting in two different times.
- query time ( without index on that field)
- indexing time. ( when we create index)
for that reason you got different result for same query.
Without index:
when we perform sort ( without index on sort filed) in mongodb will start sorting collection in querying time by scanning entire collection.
The direction of the sort will be forward( even marks : -1) i.e it will first touches documents on their insertion order( _id value).
When it encounters two documents with same field( marks: null for two documents), then it will arrange them with their _id value.
With index:
you created index on marks field in ascending order( Indexing is nothing but generating B-tree with key as marks).
when we try to sort the collection with marks:1 then we will get same results in indexed order.
when we try to sort the collection with marks:-1 then mongodb will start returning documents from backwards because documents already indexed (sorted) in ascending order.
That's why we got different results.
You can get more details on these queries when you perform explain() on them.
Without Index:
db.students2.find().sort({marks:-1}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "stackoverflow.students2",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [ ]
},
"winningPlan" : {
"stage" : "SORT",
"sortPattern" : {
"marks" : -1
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "COLLSCAN",
"filter" : {
"$and" : [ ]
},
"direction" : "forward"
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "sys2030",
"port" : 27017,
"version" : "3.2.22",
"gitVersion" : "105adca0d443f9a1a5abd608fd7133840a68dd"
},
"ok" : 1
}
We don't have index so mongodb starts scanning it on query time.
With Index:
db.students.find().sort({marks:-1}).explain()
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "stackoverflow.students",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [ ]
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"marks" : 1
},
"indexName" : "marks_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "backward",
"indexBounds" : {
"marks" : [
"[MaxKey, MinKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"serverInfo" : {
"host" : "sys2030",
"port" : 27017,
"version" : "3.2.22",
"gitVersion" : "105adca0d443f9a1a5abd608fd7133840a68dd"
},
"ok" : 1
}
We have index , so mongodb starts using it and simply returns the results from backwards(marks:-1).
This is the reason when we perform sort when collection is large, monogodb will give error as: sort with large amount of data , without index.
Further Reading:
https://docs.mongodb.com/manual/tutorial/sort-results-with-indexes/
https://docs.mlab.com/indexing/
answered Jan 4 at 5:16
BhuvanachanduBhuvanachandu
957
edited Jan 4 at 5:40
answered Jan 4 at 5:16
BhuvanachanduBhuvanachandu
957
answered Jan 4 at 5:16
BhuvanachanduBhuvanachandu
957
answered Jan 4 at 5:16
BhuvanachanduBhuvanachandu
957
957
add a comment |
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%2f54021955%2fhow-does-indexing-a-field-affect-the-sorting-order-of-docs-without-the-indexed-f%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
