16

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

NeverQuit
  • 45
  • 7
silent_coder
  • 6,222
  • 14
  • 47
  • 91

2 Answers2

17

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.

Oleg Valter is with Ukraine
  • 9,619
  • 8
  • 37
  • 59
Rob Moore
  • 3,343
  • 17
  • 18
  • Great notice about upper limit for first element. It's very natural and could affect performance much. =) But still hesitating between single field and composite index. Think some experiments needed. – silent_coder Mar 24 '13 at 19:56
1

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

baloo
  • 7,635
  • 4
  • 27
  • 35
  • 1
    Yes, I know about composit index. The only thing i'm afraid in that case it will be no advantages for composite query across single field query. But I don't know for sure. Think some experiments needed. – silent_coder Mar 24 '13 at 19:55
  • As long as the single field is the first field in the composite index you're good to go – baloo Mar 24 '13 at 19:58