mongodb index is created but is not used during query

Question

Hope this is not a totally stupid question. I'm creating a compound index on mongodb on fields that contain strings. After running the index creation I do not see a process that creates the index via db.currentOp(), But I do see the index if I run getIndexes().

db.sampledb.createIndex(
    {
        "DevIdent.ParametersExt. ID": 1,
        "DevIdent.Parameters.Type": 1,
        "MetaData.SessionName": 1
    },
    {
        background: false,
        name: "sample",
    }
)

Running this query with explain I always get COLLSCAN I never see an index scan instead of COLLSCAN:

db.sampledb.aggregate([
    {
        "$match": {
            "DevIdent.ParametersExt.ID": { "$regex": ".*22~44.*" },
            "DevIdent.Parameters.Type": { "$ne": "TYPICAL" },
            "MetaData.SessionName": "2021_02_09_13_31_03"
        }
    }
])

am I missing something basic ?

Answer 1

This matching filter is the problem:

"DevIdent.ParametersExt.ID": { "$regex": ".*22~44.*" }

You are searching for the text 22~44 anywhere in the DevIdent.ParametersExt.ID field. The problem is that a B-tree based index can only be used if searching can start at the very beginning of the field, which is not the case here.

However, there is no reason why the index could not be used on the other two fields. But, since you started your multi-column index on a field which can't be used, therefore Mongo is choosing to just not use this index at all.

You could consider just defining an index on the other two fields:

db.sampledb.createIndex({
    "DevIdent.Parameters.Type": 1,
    "MetaData.SessionName": 1
},
{
    background:false,
    name: "sample",
});

Assuming that these two fields have high cardinality (i.e. they are very restrictive), then Mongo may choose to use the index, despite the regex condition on the third field.