Compound index - skipping a prefix vs selecting all values of a prefix

Question

{
  field_1: "string" // can only have the value of "A" or "B",
  field_2: "numeric",
}

The above is the schema for my collection.

The following compound index exists:

{
  field_1: 1,
  field_2: 1
}

The query in question is below:

db.col.find( { field_2: { $gt: 100 } } )

This query skips the prefix field_1. Hence MongoDB does not use the compound index.

So in order to get it to use the compound index, I change the query to this:

db.col.find( { field_1: { $in: ["A", "B"] }, field_2: { $gt: 100 } } )

1. Would MongoDB use the compound index in the second query?
2. Would there be any performance benefits either way?
3. If there is a performance benefits in some case to using the second query, are there cases where the performance would actually be worst?

Answer 1

1. Yes, the query will use the index for the second query.
2. There will be some performance benefit, but that will depend on how big your collection is, how many documents the query returns compared to the whole collection etc.

You can check the execution stats for yourself by using explain.

db.col.find({field_1: {$in: ["A","B"] }, field_2: {$gt: 4}}).explain("executionStats")