......
"winningPlan" : {
            "stage" : "SORT",
            "sortPattern" : {
                "the_time" : -1
            },
            "inputStage" : {
                "stage" : "SORT_KEY_GENERATOR",
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "the_time" : {
                            "$lt" : ISODate("2010-01-01T00:00:00Z")
                        }
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "chat_id" : 1,
                            "_id" : 1
                        },
                        "indexName" : "chat_id_1__id_1"
......

{ "item": 1, "location": 1, "stock": 1 }


{ item: 1 }
{ item: 1, location: 1 }

MongoDB
        item
        item + location
        item + location + stock
        item + location()


    location
    stock
    location + stock

---

< H2 > question 1 < / H2 >

1. if the query can hit the index, it can directly give the addresses of all documents that meet the criteria. Since (IXSCAN), gets the address, not the document itself, it requires an extra step to find the actual document (FETCH);
from the address.
2. if the query does not have index support, it can only load all the data that may meet the conditions into memory, and then compare whether the conditions are met one by one, and finally get the result set (COLLSCAN);
.

if you take the second path, it is obviously quite resource-consuming and time-consuming, so all our queries should hit the index as much as possible, or partially hit the index. Your question may be that you don't understand what's going on with a partial hit index. Suppose your collection has 100w records, query criteria {"chat_id": ObjectId ("*"), "the_time": {"$lt": ISODate ("2010-01-01T00:00:00Z")} below:

• the worst-case scenario is that there is no index support, so you need to traverse 100w records to see who meets these two conditions to get the result set;
• but now there is {chat_id: 1, _ id: 1} , although it does not fully satisfy your query, but the first condition is satisfied. Suppose this condition helps you Filter 90w records (there are only 10w left), then the rest of the conditions only need to be traversed in these 10w, is it better than traversing 100w?

if you choose {_ id: 1} , it will not help the query, as in the first case; if you choose {chat_id: 1, _ id: 1} , it will at least help, so why not choose the latter?

< H2 > question two < / H2 >

the first thing to understand is that even if the same condition is run twice with the same index, the execution time is not necessarily the same, because the pressure on the server is not necessarily the same. Then the two indexes you mentioned {chat_id,third_field} and {chat_id,the_time} have almost no difference in their effect on your execution conditions. Objectively speaking, if the two indexes are the most efficient, you will always choose a faster one. Once the faster one is selected, the execution plan cache will ensure that it is always used before restart, rather than evaluating it every time (waste of resources). So it doesn't make any sense to compare the two here.

< H2 > other < / H2 >

db.collection.find({"chat_id" : ObjectId("*********"), "the_time":{"$lt" : ISODate("2010-01-01T00:00:00Z")}}).sort({"the_time":-1}).explain("executionStats")

the best index to satisfy this query should be {chat_id: 1, the_time:-1}

.