What if some documents don't have a field that is part of an index?-CodePudding

A collection has an indexed involved field_A. But field_A is not required. So what happens if some documents do not have this field? Will the index still work for documents that do have this field?

CodePudding user response：

Yes , index will work for the documents that have the field available and indexed , but you may look on the options to create sparse or partial type of indices which add some additional optimisation in certain cases ...

P.S. In regular indices for documents that miss the field in the index this is seen as null value ... , so if you search by field_A: null you will find those documents missing the field and those that are equal to null ...

CodePudding user response：

Yes it works, here is a test:

db.collection.createIndex({ field_A: 1 });

for (let i = 0; i < 100; i  )
   db.collection.insertOne({ field_B: i });

db.collection.stats(1024).indexSizes

{ "_id_" : 20, "field_A_1" : 20 }

You see index field_A_1 has a size of 20 kiByte. This behavior is different to most relational DBMS database where such index would have a size of zero.

The index is also used by your query, if you use the field:

db.collection.find({ field_B: 1 }).explain().queryPlanner.winningPlan;

{
    "stage" : "COLLSCAN",
    "filter" : {
        "field_B" : {
            "$eq" : 1
        }
    }
}

db.collection.find({ field_A: null, field_B: 1 }).explain().queryPlanner.winningPlan;

{
    "stage" : "FETCH",
    "inputStage" : {
        "stage" : "IXSCAN",
        "keyPattern" : {
            "field_A" : 1
        },
        "indexName" : "field_A_1",
        "indexBounds" : {
            "field_A" : [
                "[undefined, undefined]",
                "[null, null]"
            ]
        }
    }
}