Query result doesn't maintain the order as per the insertion order if it contains individual indexes. If the mongo Query contain composite indexes, will the result data order is maintained as per the insertion order?
Asked
Active
Viewed 223 times
0
-
I'm not aware of any guarantee that mongodb will maintain insertion order under _any_ circumstances unless you explicitly sort the result by an ever increasing key. – Joachim Isaksson Jun 19 '14 at 05:59
-
@JoachimIsaksson ObjectId's are monotonic and therefore always increase in value. You would have to have some messed up time sync situation on your server and application instances for this to be in issue, and there is always the option to delegate this assignment to the database server which helps mitigate this somewhat. – Neil Lunn Jun 19 '14 at 06:08
-
@NeilLunn Yes, however I know of many installations that don't use an ObjectId as _id. Also, I don't think he is asking how to sort the result in that order (since it should work well even in the presence of unique indexes), but if the order is maintained without explicitly sorting it. – Joachim Isaksson Jun 19 '14 at 06:12
-
@JoachimIsaksson Many may do so. Generally it is a good idea to keep this though unless there is a specific reason to do otherwise. I find the question to be about how things are ordered when an index is chosen. The answer is if you do not want that order then you "sort" by something relative to what reflects the insertion order. In most cases this is likely the default `_id` if not then there are other possibilities or even have a "timestamp" field that you think you can rely on. – Neil Lunn Jun 19 '14 at 06:17
-
Can you explain what query you are performing for unique indexes to prevent order? – Sammaye Jun 19 '14 at 07:06
-
Sorry, i was about to ask for individual indexes not unique indexes – Vipin Jun 19 '14 at 07:30
-
@JoachimIsaksson - capped collections maintain insertion order. – Christian P Jun 20 '14 at 13:51
-
@ChristianP Good point, however the use cases for them are a bit special. – Joachim Isaksson Jun 20 '14 at 14:44
3 Answers
3
It doesn't matter if you have a unique index or not, MongoDB WILL NOT return in insertion order unless you sort with an index which is representative of insertion order (a unique index actually isn't, it is a special type of index).
Instead it will return in natural order (not actually $natural since that is a type of disk order).
This natural order is in fact the order specified by the internal linked lists as I actually explained here: Mongo 2.6 indexing - query result order and as @kwolfe explained how to actually get sorted results.
1
The index chosen will depend on the fields used in your query and the order of results will be reflected by the index ordering.
So with data like this:
{ "a": 5, "b:" 2 }
{ "a": 5, "b": 1 }
{ "a": 1, "b": 7 }
And a compound index:
db.collection.ensureIndex({ "a": 1, "b": 1 })
A query that can use this index will order the fields by the index in the result:
{ "a": 1, "b": 7 }
{ "a": 5, "b": 1 }
{ "a": 5, "b:" 2 }
If you want to keep the insertion order then you can use the $natural modifier in the .sort():
db.collection.find({ a: { "$gte": 1 } }).sort({ "$natural": 1 })
To order by how the documents are found on disk. Or you can simply just specify the _id as the "sort"
db.collection.find({ a: { "$gte": 1 } }).sort({ "_id": 1 })
This will force the order of the results to maintain their insertion order by the _id key and it's increasing value. Requiring of course that that is true, which it should be if you are using the default ObjectId implementation.
Neil Lunn
- 148,042
- 36
- 346
- 317
-
2I may be wrong, but doesn't `$natural` just use position of the document in the file as a sort criteria? That could be out of insertion order if a document slot is reused due to a deleted document, no? – Joachim Isaksson Jun 19 '14 at 06:05
-
@JoachimIsaksson Fair point, the other case is to simply sort on the `_id` key as the order should be maintained. It's all a matter of whether the documents regularly move around due to outgrowing their allocated space. But again that is something that you really should be trying to avoid. – Neil Lunn Jun 19 '14 at 06:13
-
Thanks for the update. But sorting result data on (_id) or time-stamp will include additional burden to the system, in case if we try to retrieve millions of records. – Vipin Jun 19 '14 at 06:40
-
@user3318102 The idea is to use an "indexed" field regardless. The difference of course being that you are loading an additional index to perform the sort, or discarding the use of the index in sorting in the case of `$natural`. Otherwise the order of the index selected by the fields present in your query is respected. – Neil Lunn Jun 19 '14 at 06:54
1
If you need to preserve insertion order in a collection you need to use capped collections. For capped collections MongoDB automatically preserves the insertion order.
From the capped collection docs:
Capped collections guarantee preservation of the insertion order. As a result, queries do not need an index to return documents in insertion order. Without this indexing overhead, they can support higher insertion throughput.
Capped collections guarantee that insertion order is identical to the order on disk (natural order) and do so by prohibiting updates that increase document size. Capped collections only allow updates that fit the original document size, which ensures a document does not change its location on disk.
Capped collections automatically remove the oldest documents in the collection without requiring scripts or explicit remove operations.
But capped collections have some major drawbacks:
- You must predefine the size of the collection
- You can't delete documents from a capped collection (you can only remove all documents from the collection)
- You can update your documents but the updates can't cause documents to grow
Christian P
- 12,032
- 6
- 60
- 71