Mongodb query does not use prefix on compound index with text field

Question

I've created the following index on my collection:

db.myCollection.createIndex({
  user_id: 1,
  name: 'text'
})

If I try to see the execution plan of a query containing both fields, like this:

db.getCollection('campaigns').find({ 
    user_id: ObjectId('xxx')
   ,$text: { $search: 'bla' } 
}).explain('executionStats')

I get the following results:

...
"winningPlan" : {
    "stage" : "TEXT",
    "indexPrefix" : {
        "user_id" : ObjectId("xxx")
    },
    "indexName" : "user_id_1_name_text",
    "parsedTextQuery" : {
        "terms" : [ 
            "e"
        ],
        "negatedTerms" : [],
        "phrases" : [],
        "negatedPhrases" : []
    },
    "inputStage" : {
        "stage" : "TEXT_MATCH",
        "inputStage" : {
            "stage" : "TEXT_OR",
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "user_id" : 1.0,
                    "_fts" : "text",
                    "_ftsx" : 1
                },
                "indexName" : "user_id_1_name_text",
                "isMultiKey" : true,
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 1,
                "direction" : "backward",
                "indexBounds" : {}
            }
        }
    }
}
...

As stated in the documentation, MongoDB can use index prefixes to perform indexed queries.

Since user_id is a prefix for the index above, I'd expect that a query only by user_id would use the index, but if I try the following:

db.myCollection.find({ 
    user_id: ObjectId('xxx')
}).explain('executionStats')

I get:

...
"winningPlan" : {
    "stage" : "COLLSCAN",
    "filter" : {
        "user_id" : {
            "$eq" : ObjectId("xxx")
        }
    },
    "direction" : "forward"
},
...

So, it is not using the index at all and performing a full collection scan.

Answer 1

In general MongoDB can use index prefixes to support queries, however compound indexes including geospatial or text fields are a special case of sparse compound indexes. If a document does not include a value for any of the text index field(s) in a compound index, it will not be included in the index.

In order to ensure correct results for a prefix search, an alternative query plan will be chosen over the sparse compound index:

If a sparse index would result in an incomplete result set for queries and sort operations, MongoDB will not use that index unless a hint() explicitly specifies the index.

Setting up some test data in MongoDB 3.4.5 to demonstrate the potential problem:

db.myCollection.createIndex({ user_id:1, name: 'text' }, { name: 'myIndex'})

// `name` is a string; this document will be included in a text index
db.myCollection.insert({ user_id:123, name:'Banana' })

// `name` is a number; this document will NOT be included in a text index
db.myCollection.insert({ user_id:123, name: 456 })

// `name` is missing; this document will NOT be included in a text index
db.myCollection.insert({ user_id:123 })

Then, forcing the compound text index to be used:

db.myCollection.find({user_id:123}).hint('myIndex')

The result only includes the single document with the indexed text field name, rather than the three documents that would be expected:

{
  "_id": ObjectId("595ab19e799060aee88cb035"),
  "user_id": 123,
  "name": "Banana"
}

This exception should be more clearly highlighted in the MongoDB documentation; watch/upvote DOCS-10322 in the MongoDB issue tracker for updates.