1

In MongoDB, I have a collection with a date field called last_updated I would like to index. Reason being, I am often querying on a date range of that field in addition to sorting by that field and feel as though I can speed the process up. I would like to add as a side note: this collection does see a lot of writes and updates.

However, I would like to know the implications of doing so. My understanding is that updates are the heaviest transaction in a b-tree index structure and would poorly affect performance. Would anyone mind explaining whether it would or wouldn't and if not why it would be just as normal as indexing on another date field (one that is not updated).

0

Hi It will affect but not greatly.what happens is whenever you made change in collection index will be updated and during index update your collection will be locked .So to overcome this you can use index in background by using: db.collection.createIndex( { last_updated: 1}, {background: true} )

it will let you use your collection while updating so you will never found any kind of disruption because of your index.

As you have mentioned that you do sorting on "last_updated".I strongly recommend you to use index .you will have high read performance at a minimal indexing cost.

7
  • FYI: "After MongoDB finishes building the index, background-built indexes are functionally identical to any other index.": docs.mongodb.org/manual/tutorial/…. Index update behaviour will vary depending on the storage engine used (MongoDB 3.0+) and the nature of the update.
    – Stennie
    Oct 8 '15 at 11:40
  • But my point is when we are writing heavily on a collection then how you will come to know that your writes affecting the collection ?????? Because they also update index and during index updating collection is locked .. That is where we call performance degrading.. So to minimize that effect i suggest to run index in background so that no collection lock occur .. Hope i made my point clear to you Oct 8 '15 at 14:21
  • updates are heaviest on b-tree index structures because not only are you finding a node and inserting but also deleting the node with the fields previous value. (this is to my understanding) my question is not how to run an index in the background but how will updates/writes be affected because the indexed field is constantly updated.
    – Dap
    Oct 8 '15 at 16:17
  • It will work in background deletion and addition will not affect your read or write performance . Oct 8 '15 at 16:19
  • 2
    @GaganSidhu To be clear on background indexing: this is only an option that affects the initial index build. Once the index is created, future updates work identically to an index that was initial created in the foreground (there are no "background updates" to an existing index). For a production deployment it is generally a good idea to create indexes in the background to avoid or using a rolling index build (replica set). My take was that the original question was asking for clarification of behaviour for updates.
    – Stennie
    Oct 8 '15 at 23:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.