MongoDb Full text search with partial search

Question

I am using mongodb 3.6 and i it has almost 5-6 lkhs document in my collection. i want to search like full text search as well as partial search. `

 db.temp.find( {$and : [{"status" : {"$in" : [ 1,  2]} },
                          {$or:[ { $text: { $search: "school" }}
                                 ,{ cname : /school/i}
                                 ,{ name : /school/i}
                                  ]}  ]},
         {cname:1,name:1,followers:1,status :1, score: { $meta: 
          "textScore" } } ).sort( { score: { $meta: "textScore" 
          },status :-1 ,followers :-1 } )

` Index on temp collection.

  db.temp.createIndex(
   {
     name: "text",
     cname: "text"
  },
  {
    weights: {
     name: 4,
   cname: 2
     }
   }
   )
     db.getCollection("temp").createIndex({ 
            "cname": 1
        }, {background: true})


     db.getCollection("temp").createIndex({ 
          "status" : -1.0, 
         "followers" : -1.0
          }, {background: true});


        db.getCollection("temp").createIndex({ 
           "name": 1
           }, {background: true})`

Document are like :

{ 
       "_id" : 5011.0, 
       "cname" : "samyselvik", 
       "name" : "Samy Sam", 
       "imgname" : "nrwi4769731443194380996.jpg", 
       "followers" : 1.0, 
       "status" : 1.0, 
        "createdat" : 1443194421532.0
    }

When i check execution('executionStats'),it shows

"executionStats" :{
                    "executionSuccess" : true, 
                    "nReturned" : 363.0, 
                    "executionTimeMillis" : 894.0, 
                    "totalKeysExamined" : 921424.0, 
                    "totalDocsExamined" : 372.0, 
                    "executionStages" : {
                        "stage" : "PROJECTION", 
                        "nReturned" : 363.0, 
                        "executionTimeMillisEstimate" : 808.0, 
                        "works" : 921803.0, 
                        "advanced" : 363.0, 
                        "needTime" : 921439.0, 
                        "needYield" : 0.0, 
                        "saveState" : 7234.0, 
                        "restoreState" : 7234.0, 
                        "isEOF" : 1.0, 
                        "invalidates" : 0.0, 
                        "transformBy" : {
                            "cname" : 1.0, 
                            "name" : 1.0, 
                            "followers" : 1.0, 
                            "score" : {
                                "$meta" : "textScore"
                            }
                        }, 
                        "inputStage" : {
                            "stage" : "SORT", 
                            "nReturned" : 363.0, 
                            "executionTimeMillisEstimate" : 774.0, 
                            "works" : 921803.0, 
                            "advanced" : 363.0, 
                            "needTime" : 921439.0, 
                            "needYield" : 0.0, 
                            "saveState" : 7234.0, 
                            "restoreState" : 7234.0, 
                            "isEOF" : 1.0, 
                            "invalidates" : 0.0, 
                            "sortPattern" : {
                                "score" : {
                                    "$meta" : "textScore"
                                }, 
                                "status" : -1.0, 
                                "followers" : -1.0
                            }, 
                            "memUsage" : 131542.0, 
                            "memLimit" : 33554432.0, 
                            "limitAmount" : 500.0, 
                            "inputStage" : {
                                "stage" : "SORT_KEY_GENERATOR", 
                                "nReturned" : 363.0, 
                                "executionTimeMillisEstimate" : 730.0, 
                                "works" : 921439.0, 
                                "advanced" : 363.0, 
                                "needTime" : 921075.0, 
                                "needYield" : 0.0, 
                                "saveState" : 7234.0, 
                                "restoreState" : 7234.0, 
                                "isEOF" : 1.0, 
                                "invalidates" : 0.0, 
                                "inputStage" : {
                                    "stage" : "FETCH", 
                                    "filter" : {
                                        "status" : {
                                            "$in" : [
                                                1.0, 
                                                2.0
                                            ]
                                        }
                                    }, 
                                    "nReturned" : 363.0, 
                                    "executionTimeMillisEstimate" : 719.0, 
                                    "works" : 921438.0, 
                                    "advanced" : 363.0, 
                                    "needTime" : 921074.0, 
                                    "needYield" : 0.0, 
                                    "saveState" : 7234.0, 
                                    "restoreState" : 7234.0, 
                                    "isEOF" : 1.0, 
                                    "invalidates" : 0.0, 
                                    "docsExamined" : 363.0, 
                                    "alreadyHasObj" : 9.0, 
                                    "inputStage" : {
                                        "stage" : "OR", 
                                        "nReturned" : 363.0, 
                                        "executionTimeMillisEstimate" : 697.0, 
                                        "works" : 921438.0, 
                                        "advanced" : 363.0, 
                                        "needTime" : 921074.0, 
                                        "needYield" : 0.0, 
                                        "saveState" : 7234.0, 
                                        "restoreState" : 7234.0, 
                                        "isEOF" : 1.0, 
                                        "invalidates" : 0.0, 
                                        "dupsTested" : 399.0, 
                                        "dupsDropped" : 36.0, 
                                        "recordIdsForgotten" : 0.0, 
                                        "inputStages" : [
                                            {
                                                "stage" : "TEXT", 
                                                "nReturned" : 9.0, 
                                                "executionTimeMillisEstimate" : 0.0, 
                                                "works" : 21.0, 
                                                "advanced" : 9.0, 
                                                "needTime" : 11.0, 
                                                "needYield" : 0.0, 
                                                "saveState" : 7234.0, 
                                                "restoreState" : 7234.0, 
                                                "isEOF" : 1.0, 
                                                "invalidates" : 0.0, 
                                                "indexPrefix" : {

                                                }, 
                                                "indexName" : "name_text_cname_text", 
                                                "parsedTextQuery" : {
                                                    "terms" : [
                                                        "sam"
                                                    ], 
                                                    "negatedTerms" : [

                                                    ], 
                                                    "phrases" : [

                                                    ], 
                                                    "negatedPhrases" : [

                                                    ]
                                                }, 
                                                "textIndexVersion" : 3.0, 
                                                "inputStage" : {
                                                    "stage" : "TEXT_MATCH", 
                                                    "nReturned" : 9.0, 
                                                    "executionTimeMillisEstimate" : 0.0, 
                                                    "works" : 21.0, 
                                                    "advanced" : 9.0, 
                                                    "needTime" : 11.0, 
                                                    "needYield" : 0.0, 
                                                    "saveState" : 7234.0, 
                                                    "restoreState" : 7234.0, 
                                                    "isEOF" : 1.0, 
                                                    "invalidates" : 0.0, 
                                                    "docsRejected" : 0.0, 
                                                    "inputStage" : {
                                                        "stage" : "TEXT_OR", 
                                                        "nReturned" : 9.0, 
                                                        "executionTimeMillisEstimate" : 0.0, 
                                                        "works" : 21.0, 
                                                        "advanced" : 9.0, 
                                                        "needTime" : 11.0, 
                                                        "needYield" : 0.0, 
                                                        "saveState" : 7234.0, 
                                                        "restoreState" : 7234.0, 
                                                        "isEOF" : 1.0, 
                                                        "invalidates" : 0.0, 
                                                        "docsExamined" : 9.0, 
                                                        "inputStage" : {
                                                            "stage" : "IXSCAN", 
                                                            "nReturned" : 9.0, 
                                                            "executionTimeMillisEstimate" : 0.0, 
                                                            "works" : 10.0, 
                                                            "advanced" : 9.0, 
                                                            "needTime" : 0.0, 
                                                            "needYield" : 0.0, 
                                                            "saveState" : 7234.0, 
                                                            "restoreState" : 7234.0, 
                                                            "isEOF" : 1.0, 
                                                            "invalidates" : 0.0, 
                                                            "keyPattern" : {
                                                                "_fts" : "text", 
                                                                "_ftsx" : 1.0
                                                            }, 
                                                            "indexName" : "name_text_cname_text", 
                                                            "isMultiKey" : true, 
                                                            "isUnique" : false, 
                                                            "isSparse" : false, 
                                                            "isPartial" : false, 
                                                            "indexVersion" : 2.0, 
                                                            "direction" : "backward", 
                                                            "indexBounds" : {

                                                            }, 
                                                            "keysExamined" : 9.0, 
                                                            "seeks" : 1.0, 
                                                            "dupsTested" : 9.0, 
                                                            "dupsDropped" : 0.0, 
                                                            "seenInvalidated" : 0.0
                                                        }
                                                    }
                                                }
                                            }, 
                                            {
                                                "stage" : "IXSCAN", 
                                                "filter" : {
                                                    "$or" : [
                                                        {
                                                            "cname" : {
                                                                "$regex" : "Sam", 
                                                                "$options" : "i"
                                                            }
                                                        }
                                                    ]
                                                }, 
                                                "nReturned" : 193.0, 
                                                "executionTimeMillisEstimate" : 357.0, 
                                                "works" : 460693.0, 
                                                "advanced" : 193.0, 
                                                "needTime" : 460499.0, 
                                                "needYield" : 0.0, 
                                                "saveState" : 7234.0, 
                                                "restoreState" : 7234.0, 
                                                "isEOF" : 1.0, 
                                                "invalidates" : 0.0, 
                                                "keyPattern" : {
                                                    "cname" : 1.0
                                                }, 
                                                "indexName" : "cname_1", 
                                                "isMultiKey" : false, 
                                                "multiKeyPaths" : {
                                                    "cname" : [

                                                    ]
                                                }, 
                                                "isUnique" : false, 
                                                "isSparse" : false, 
                                                "isPartial" : false, 
                                                "indexVersion" : 2.0, 
                                                "direction" : "forward", 
                                                "indexBounds" : {
                                                    "cname" : [
                                                        "[\"\", {})", 
                                                        "[/Sam/i, /Sam/i]"
                                                    ]
                                                }, 
                                                "keysExamined" : 460692.0, 
                                                "seeks" : 1.0, 
                                                "dupsTested" : 0.0, 
                                                "dupsDropped" : 0.0, 
                                                "seenInvalidated" : 0.0
                                            }, 
                                            {
                                                "stage" : "IXSCAN", 
                                                "filter" : {
                                                    "$or" : [
                                                        {
                                                            "name" : {
                                                                "$regex" : "Sam", 
                                                                "$options" : "i"
                                                            }
                                                        }
                                                    ]
                                                }, 
                                                "nReturned" : 197.0, 
                                                "executionTimeMillisEstimate" : 318.0, 
                                                "works" : 460724.0, 
                                                "advanced" : 197.0, 
                                                "needTime" : 460526.0, 
                                                "needYield" : 0.0, 
                                                "saveState" : 7234.0, 
                                                "restoreState" : 7234.0, 
                                                "isEOF" : 1.0, 
                                                "invalidates" : 0.0, 
                                                "keyPattern" : {
                                                    "name" : 1.0
                                                }, 
                                                "indexName" : "name_1", 
                                                "isMultiKey" : false, 
                                                "multiKeyPaths" : {
                                                    "name" : [

                                                    ]
                                                }, 
                                                "isUnique" : false, 
                                                "isSparse" : false, 
                                                "isPartial" : false, 
                                                "indexVersion" : 2.0, 
                                                "direction" : "forward", 
                                                "indexBounds" : {
                                                    "name" : [
                                                        "[\"\", {})", 
                                                        "[/Sam/i, /Sam/i]"
                                                    ]
                                                }, 
                                                "keysExamined" : 460723.0, 
                                                "seeks" : 1.0, 
                                                "dupsTested" : 0.0, 
                                                "dupsDropped" : 0.0, 
                                                "seenInvalidated" : 0.0
                                            }
                                        ]
                                    }
                                }
                            }
                        }
                    }, 
                    "allPlansExecution" : [

                    ]
                }

temp Collection have total 4.60 lkhs documents and keys examined are more then documents.please let me know how to optimize this query so that i can use both full text as well as partial search?

Answer 1

The brief explanation of the query stages:

• regex over name takes 0.3 sec, examines 460k keys, and returns 197 docs
• regex over cname takes 0.4 sec, examines 460k keys, and returns 193 docs
• full text examines 9 keys and returns 9 docs instantly

Intersection of all 3 or conditions return 363 docs in total in 0.7 sec. It checked 460k + 460k = 920k keys.

Following stages read the documents, apply the status filter, sort results in memory and project the response take less than 50 millis each, which gives you the total time 0.9 sec, which leave little room for optimisation there.

Assuming you cannot do it without regex. First of all check if .hint("name_text_cname_text") is quicker. Sometimes regexps are more efficient with collscan https://docs.mongodb.com/manual/reference/operator/query/regex/#index-use .

Next, you can optimise the data by combining cname and name into a single field to reduce number of regex searches:

{ 
   "_id" : 5011.0, 
   "cname" : "samyselvik", 
   "name" : "Samy Sam", 
   "search_name" : "samyselvik Samy Sam",
   "imgname" : "nrwi4769731443194380996.jpg", 
   "followers" : 1.0, 
   "status" : 1.0, 
   "createdat" : 1443194421532.0
}

You will need to update all your documents once and update your app to ensure future docs will have valid search_name field.

The query will be:

db.temp.find( 
    { $and : [
        { "status" : { "$in" : [1, 2]} },
        { $or: [ 
            { $text: { $search: "school" } },
            { search_name : /school/i}
        ] }  
    ] },
    { cname:1, name:1, followers:1, status :1, score: { $meta: "textScore" } } 
).sort( { score: { $meta: "textScore" }, status :-1, followers :-1 } )

It should save around 0.2 sec, so no magic here. Regexps are expensive but in some scenarios unavoidable.