Mongodb performance: compound query vs moving to other collection

Question

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).

Answer 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