mongodb index strategy for range query with differ

Question

Almoust all my documents include 2 fields, start timestamp and finish timestamp. And in each my query I need to get elements which is 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 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

Answer 1

Too 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})

You query will perform (potentially much) better if you query 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.

HTH - Rob.

Answer 2

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