1

Is there a way to specify the natural ordering of data in mongodb, similar to how a primary index would order data in a RDBMS table?

My use case is that all my queries return data sorted by a date field, say birthday. According to MongoDB: Sorting and Natural Order, the natural order for a standard (non-capped) collection is roughly the insertion order, but not guaranteed. This would imply sorting is needed after the data is retrieved.

Stennie
  • 63,885
  • 14
  • 149
  • 175
ltfishie
  • 2,917
  • 6
  • 41
  • 68
  • Some related discussion on natural order: [What does Mongo sort on when no sort order is specified?](http://stackoverflow.com/questions/11599069/what-does-mongo-sort-on-when-no-sort-order-is-specified/11599283). – Stennie Jul 22 '12 at 11:57

4 Answers4

1

If you are looking for something to act like the primary index in a RDBMS then sort by _id. It will be roughly insert order since the _id is prefixed with timestamp. If you try to use $natural order it will cause it to miss indexes.

Ben
  • 9,725
  • 6
  • 23
  • 28
  • Sorry for the confusion, I have updated my post to make it clear that I am not looking to sort data by the insertion date. I am interested to know if there is a way to define the natural order to sorting operation is not needed. – ltfishie Jul 16 '12 at 04:59
1

I believe what you are referring to is a clustered index, not a primary index.

MongoDB 2.0 does not have a clustered index feature, so a regular index on date would be your most efficient option to retrieve.

It's probably premature optimization to think about the physical order on disk with MongoDB. MongoDB uses memory-mapped files, so depending on your working set + queries + RAM you may not need to load data from disk as often as expected.

Stennie
  • 63,885
  • 14
  • 149
  • 175
0

Also, I would add that you should look into using the built-in timestamps in the document IDs instead of relying on a separate date field, as it allows you to store less data and removes an index.

Jason MongoHQ

Jason McCay
  • 3,295
  • 1
  • 13
  • 8
  • Thanks for your answer. Sorry I wasn't clear on my question. I am not trying to sort the document by their insertion time – ltfishie Jul 16 '12 at 04:55
0

I guess it would be difficult to achieve what you want without the help of indexes. To support sharding, the _id field in MongoDB takes values based on the timestamp at the moment the document is created. As a consequence, you can't have them monotonically increasing unlike the identity column in RDBMS table..I think you must create an index on Birthdate column if all your queries return documents sorted in the order of Birthdate. Once the index is created, the queries become efficient enough..
Refer this:
MongoDB capped collection and monotically increasing index

Community
  • 1
  • 1
Aafreen Sheikh
  • 4,949
  • 6
  • 33
  • 43
  • To be clear, the [default ObjectIDs](http://www.mongodb.org/display/DOCS/Object+IDs) are not intended to "support sharding" and generally [do **not**](http://docs.mongodb.org/manual/faq/sharding/#can-you-shard-on-the-id-field) make a [**good** shard key](http://www.mongodb.org/display/DOCS/Choosing+a+Shard+Key). – Stennie Jul 17 '12 at 21:44
  • Agreed. It will lead to all new data hammerring a particular shard. In other words, data distribution will follow time-distribution, which is not really helpful.. – Aafreen Sheikh Jul 18 '12 at 08:49