In MongoDB, which field order is best for a compound index when mixing $in and equality conditions?

Working with Data
, ,
1

I have a query like this in MongoDB:

db.people.find({
  city: { $in: ["Chicago", "LA", "NY"] },
  intention: "travel"
})

  • intention is always a single fixed value (equality).

  • city can take multiple values via $in.

    • there are roughly 10 intentions and 10 cities, evenly distributed.

I want to create a compound index to make this query as efficient as possible.

My understanding is that the ESR rule (Equality → Sort → Range) suggests putting equality fields first. But since I have one single-value equality (intention) and one multi-value equality (city with $in), I’m not sure which should go first in the index.

So my questions are:

  1. Should the index be { intention: 1, city: 1 } or { city: 1, intention: 1 } for best performance?

  2. Does MongoDB treat $in as multiple equalities (so order doesn’t matter), or more like a range condition in this case?

Any clarification on how MongoDB evaluates compound indexes with $in would be appreciated.