CODEWITHSUNDEEP
mongodbmongodb-query
Hafez
Hafez

Reputation: 1440

MongoDB performance: $and vs. single object with multiple keys

I have an internal service that does some operations on an order, it has a built-in required filter, and the service users can pass additional filter.

Two approaches to achieve the same thing: A) Using $and:

async function getOrders ({ optionalFilter = {} }) {
  const baseFilter = { amount: { $gt: 10 } };
  const mergedFilter = { $and: [baseFilter, optionalFilter] };

  return await Order.find(mergedFilter);
}

B) Merging all in the same object

async function getOrders ({ optionalFilter = {} }) {
  const baseFilter = { amount: { $gt: 10 } };
  const mergedFilter = { ...baseFilter, ...optionalFilter };

  return await Order.find(mergedFilter);
}

I prefer the first approach because it allows me to do the following without overwriting the $gt: 10 rule while the second would break the code by overwriting the internal rule.

getOrders({ optionalFilter: { amount: { $lt: 50 } } });

My question is, is there any advantage (performance or otherwise) of choosing one over the other?

Upvotes: 1

Views: 196

Answers (1)

AlexZeDim
AlexZeDim

Reputation: 4352

My question is, is there any advantage (performance or otherwise) of choosing one over the other?

  • Short answer: No. If you are not using (compound) indexes

  • Long Answer: Okay, let's test this up, right?

So let's take a collection of ~100k documents, and un-indexed array field:

  1. {asset_class: "COMMDTY"} unIndexed

  2. with {$and: [{item_subclass: "Herb", asset_class: "COMMDTY"}]} and twoFields unIndexed

  3. without {item_subclass: "Herb", asset_class: "COMMDTY"} twoFields unIndexed

Okay, there is 114ms difference between using $and and without. But does it means to stop you using $and and avoid it? Of course, not. But as much field have added, the more slower Mongo will become. But this whole picture changes, when I add an already indexed field to a query.

{expansion: "BFA", asset_class: "COMMDTY"} indexed two

So, if you add more fields via your mergedFilter then make sure that your index (if you are using them) can be used, because as for compound index field order is very meaningful. So the query: {asset_class: "COMMDTY", expansion: "BFA" } will goes already in: 11ms

Upvotes: 1

Related Questions