CODEWITHSUNDEEP
mongodbperformanceaggregate
Joe
Joe

Reputation: 13141

How to speed up agregate queries in MongoDB

I am running examples of aggregate queries similar to this:

https://www.compose.com/articles/aggregations-in-mongodb-by-example/

db.mycollection.aggregate([
  { 
    { $match: {"nested.field": "1110"}}, {
    $group: {
      _id: null,
      total: {
        $sum: "$nested.field"
      },
      average_transaction_amount: {
        $avg: "$nested.field"
      },
      min_transaction_amount: {
        $min: "$nested.field"
      },
      max_transaction_amount: {
        $max: "$nested.field"
      }
    }
  }
]);

One collection that I created have 5,000,000 inserted big JSON documents (around 1,000 K->V pairs, some are nested).

Before adding index on one nested field - it takes around 5min to do count of that field. After adding index - for count it takes less than a second (which is good).

Now I am trying to do SUM or AVG or any other like example above - it takes minutes (not seconds). Is there a way to improve aggregate queries in MongoDB?

Thanks!

Upvotes: 0

Views: 1583

Answers (1)

bgraham
bgraham

Reputation: 2027

Unfortunately, group currently does not use indexes in mongodb. Only sort and match can take advantage of indexes. So the query as you wrote it is as optimized as it could be.

There are a couple things you could do. For max and min, you could just query them instead of using the aggregation framework. You can than sort by $nested.field and take just one. You can put an index on $nested.field and you can then sort ascending or descending with the same index.

If you have any control over when the data is inserting, and the query is as simple as it looks, you could keep track of the data yourself. So you could have a table in mongo where the collection has the "Id" or whatever you are grouping on and have fields for "total" and "sum". You could increment them on inserts and then getting the total and averages would be fast queries. Not sure if that's an option for your situation, but its the best you can do.

Generally, mongo is super fast. In my opinion, the only place its not quite as good as SQL is aggregation. The benefits heavily outweigh the struggles to me. I generally maintain separate reporting collections for this kind of situation as I recommended.

Upvotes: 3

Related Questions