1

My application uses mongodb to store order information. At some point orders are sent after which I need to generate some reporting of all orders in a timerange.

To give an idea of my order document:

{
    status: [type: String, range: 6 different statuses],
    timestamp: [date],
    customer: 
        {
            id: [int, range: about 50]
            other non searchable indexed data
        }
    other non searchable indexed data
}

With a single collection I would query:

{ "status": "SENT", "customer.id" : ..., "timestamp" : {$gte: ..., $lt: ...}}

With an indexes like:

{ "status" : 1, "timestamp" : -1, "customer.id" : 1}
{ "status" : 1, "timestamp" : 1, "customer.id" : 1}

I would be able to query these rows. However I found that multiple criteria in a query would slow it down.

So my question is:

Is it better (performance wise, but also best practices) to use compound indexes with compound queries or to have multiple collections for different status (like order_sent, order_new, order_finished) when the database has millions of orders.

Or in other words, how much of a performance impact does extra criteria have on a correctly index query (and are the proposed indexes viable for this situation).

1

Since 2.6 MongoDB supports index intersection.

https://docs.mongodb.com/manual/core/index-intersection/

To my general experience your queries are not complex enough to be worried.

You should of course create indices for status (especially since it is a string), customer.id, and timestamp but leave the rest to the MongoDB internals. You can of course create a compound index too, and make some benchmarks to see for yourself using .hint() to force MongoDB to use a specific index.

https://docs.mongodb.com/manual/reference/method/cursor.hint/#cursor.hint

You should also know, that the ObjectId contains a timestamp corresponding to the document creation time already which comes in handy as there is always an index on _id.

https://docs.mongodb.com/manual/reference/method/ObjectId/#ObjectId

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.