Index on Date column

I have a Timestamp field in my documents for one of the collection. Is it a good idea to have a index on the Timestamp column ? I have a requirement in which I need to retrieve the documents between the certain dates. Trying to figure out the best way to retrieve the documents.

Thank you,
Jason

Hello @Jason_Widener,

Is it a good idea to have a index on the Timestamp column ?

Yes, it is generally a good idea to have an index on a field used in a query criteria. This is really useful when there are a large number of documents (e.g., a million) in the collection. The index will be used to run the query fast.

An index created on one field is called as a Single Field Index. This index is applied on a query with condition to retrieve documents for a range of values (e.g., between two Timestamp values) efficiently.

Typically, you use a query like this to get the documents for a range of values:

db.collection.find( { timestamp: { $gte: <start_value>, $lte: <end_value> } } )

References:

Thanks for your response @Prasad_Saya . Yes it is good idea to have index but is it good idea to have an index on Timestamp field/column ? Because anyways timestamp field is mostly going to be unique in all the documents. Unless two documents were inserted at exact same date and time.

Timestamp is just a field in a document. You create an index on a field for the purpose of performance of queries and sort operations. Whether field is unique value or not, and the type of data (can be date, timestamp, string, number, etc) doesn’t matter. Indexing and field properties are two different things.

If your application requires that the field be unique, then an index of type unique can be created (see Unique Indexes).

Awesome, thanks @Prasad_Saya

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.