CODEWITHSUNDEEP
mongodbindexingnosql
silent_coder
silent_coder

Reputation: 6502

mongodb index strategy for range query with different fields

Almost all my documents include 2 fields, start timestamp and final timestamp. And in each query, I need to retrieve elements which are in selected period of time. so start should be after selected value and final should be before selected timestamp.

query looks like

db.collection.find({start:{$gt:DateTime(...)}, final:{$lt:DateTime(...)}})

So what is the best indexing strategy for that scenario?

By the way, which is better for performance - to store date as datetimes or as unix timestamps, which is long value itself

Upvotes: 16

Views: 15501

Answers (2)

Rob Moore
Rob Moore

Reputation: 3383

To add a little more to baloo's answer.

On the time-stamp vs. long issue. Generally the MongoDB server will not see a difference. The BSON encoding length is the same (64 bits). You may see a performance different on the client side depending on the driver's encoding. As an example, on the Java side a using the 10gen driver a time-stamp is rendered as Date that is a lot heavier than Long. There are drivers that try to avoid that overhead.

The other issue is that you will see a performance improvement if you close the range for the first field of the index. So if you use the index suggested by baloo:

db.collection.ensureIndex({start: 1, final: 1})

The query will perform (potentially much) better if it is:

db.collection.find({start:{$gt:DateTime(...),$lt:DateTime(...)}, 
                    final:{$lt:DateTime(...)}})

Conceptually, if you think of the indexes as a a tree the closed range limits both sides of the tree instead of just one side. Without the closed range the server has to "check" all of the entries with a start greater than the time stamp provided since it does not know of the relation between start and final.

You may even find that that the query performance is no better using a single field index like:

db.collection.ensureIndex({start: 1})

Most of the savings is from the first field's pruning. The case where this will not be the case is when the query is covered by the index or the ordering/sort for the results can be derived from the index.

Upvotes: 17

baloo
baloo

Reputation: 7755

You can use a Compound index in order to create an index for multiple fields.

db.collection.ensureIndex({start: 1, final: 1})

Compare different queries and indexes by using explain() to get the most out of your database

Upvotes: 1

Related Questions