Mongodb performance: compound query vs moving to other collection

best practicesindexmongodb

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

Best Answer