Imagine that you have a collection with info about goods in an antique store. Each document has the following structure:

{
  id": 100,

  "category": "furniture",

  "price":1000,

  "quantity": 10
}

You've created this index:

db.goods.createIndex((price: 1, quantity: 1))

And performed the following request:

db.goods.find((quantity: (Sgte: 2), price: ($it: 1000)), (quantity: 1, id: 0})

How the following query will be executed?

Here are the options:

1. No index will be used, all results will be retrieved directly from the DB.

2. Index will be used and after that results will be retrieved from the DB.

3. The desired results will be retrieved only from the index.

4. Index will be used and after that results will be retrieved from the cache.

CodePudding user response：

No index will be used

The order of the fields listed in a compound index is important. The index will contain references to documents sorted first by the values of the item field and, within each value of the item field, sorted by values of the stock field. See Sort Order for more information.

In addition to supporting queries that match on all the index fields, compound indexes can support queries that match on the prefix of the index fields. That is, the index supports queries on the price field as well as both price and quantity fields

Reference