Next.js/MongoDB - Query Optimization-CodePudding

I am building a website using Next.js and MongoDB. On one of my website page, I have implemented filters to help search for products. To retrieve and update the filters (update item count each time a filter is changing), I have an api endpoint which query my MongoDB Collection. This specific collection contains ~200.000 items. Each item have several fields such as brand, model, place etc...

I have 9 fields which I use to filter and thus must fetch through my api each time there's a change. Therefore I have 9 queries running through my api, on for each field/filter and the query on MongoDB looks like :


  var models = await db_collection
    .aggregate([
      {
        $match: {
          $and: [filter],
        },
      },
      {
        $group: { _id: '$model', count: { $sum: 1 } },
      },
      { $sort: { _id: 1 } },
    ])
    .toArray();

The problem is that, as 9 queries are running, the update of the page (mainly due to the queries) takes ~4secs which is too long. I would like to reach <1sec. I would like to now if there is a good practice I am missing such as doing one query instead of one for each filter or maybe a database optimization on my database.

Thank you,

I have tried using a $project argument before $groupon aggregate pipeline for the query to reduce the number of field returned, using distinct and then sorting instead of aggregate but none of these solutions seem to improve efficiency.

EDIT : As suggested by R2D2, I am posting the structure of a document on MongoDB in my collection :

{
 _id : ObjectId('example_id')
 source : string
 date : date
 brand : string
 family : string
 model : string
 size : string
 color : string
 condition : string
 contact : string
 SKU : string
}

Depending on the pages, I query unique values of each field of interest (source, date, brand, family, model, size, color, condition, contact) and their count depending on filters (e.g. Number for each unique values of model for selected brands, I also query documents based on specific values of these fields.

CodePudding user response：

As mentioned, you indexes are important and if you are querying by those field I recomand to create compound indexes, see here for indexes optimisation : https://learnmongodbthehardway.com/schema/indexes/

As far as the aggregation pipeline goes, nothing is out of the ordinary, but this specific aggregation just return the number of items per model matching the criteria, not the matching document. If it is all the data you need you might find it usefull to create a new collection when you perform pre-caculation for common search daily (how many items have the color black, ...) this way, when the page loads, you don't have to look in you 200k items, but just in your pre-calculated statistical collection. Schedule a cron task or use a lambda function to invoke a route on your api that will calculate all your stats once a day and upsert them in a new collection.

Also I believe the "and" is useless useless since you can use the implicit $and. You can look for an object like :

{
 color : {$in : ['BLACK', 'BLUE']},
 size : 3
}

rather than :

[{color : 'BLACK'}, {color : 'BLUE'}, {size : 3}]

Reserve the explicit $and for when you really need it.