CODEWITHSUNDEEP
mongodbindexing
Henrique Barcelos
Henrique Barcelos

Reputation: 7900

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

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.

Upvotes: 7

Views: 2162

Answers (2)

Adam Harrison
Adam Harrison

Reputation: 3421

This behavior is due to text indexes being sparse by default:

For a compound index that includes a text index key along with keys of other types, only the text index field determines whether the index references a document. The other keys do not determine whether the index references the documents or not.

The query filter is not referencing the text index field, so the query planner won't consider this index as it can't be certain that the full result set of documents will be returned by the index.

Upvotes: 2

Stennie
Stennie

Reputation: 65313

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.

Upvotes: 6

Related Questions