How indexing on a date field that is constantly updated and or omitted affect performance?

Posted on

Question :

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).

Answer :

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.

Leave a Reply

Your email address will not be published. Required fields are marked *