MongoDB Text index search optimization

Viewed 28

When I'm trying to search for email as a phrase (inside double quotes), MongoDB performs 3 index scans for each term (username, gmail, com), then OR, and then fetch, which takes a lot of time. My text index also contains other fields that I want to search as well (not just email).

If I search for a username (prefix before @), it is lightning fast, as you can see from the right-most index scan.

My index is: { merchantId: 1, fullName: text, metadata: text }

Also, my query has limit: 20, but it does not limit the number of index scan results.

I've tried using regex with a regular index, but the index is not being used if regex does not contain a start anchor.

Is there any way to optimize the query (or index)?

Query: db.profile.find({ merchantId: ObjectId("5fa1********************"), $text: { $search: '"****@gmail.com"' } }).limit(20)

Explain:

{ queryPlanner: 
   { plannerVersion: 1,
     namespace: 'profile',
     indexFilterSet: false,
     parsedQuery: 
      { '$and': 
         [ { merchantId: { '$eq': ObjectId("5fa1********************") } },
           { '$text': 
              { '$search': '"****@gmail.com"',
                '$language': 'english',
                '$caseSensitive': false,
                '$diacriticSensitive': false } } ] },
     winningPlan: 
      { stage: 'LIMIT',
        limitAmount: 20,
        inputStage: 
         { stage: 'TEXT',
           indexPrefix: { merchantId: ObjectId("5fa1********************") },
           indexName: 'merchantId_1_fullName_text_metadata_text',
           parsedTextQuery: 
            { terms: [ 'com', 'gmail', '****' ],
              negatedTerms: [],
              phrases: [ '****@gmail.com' ],
              negatedPhrases: [] },
           textIndexVersion: 3,
           inputStage: 
            { stage: 'TEXT_MATCH',
              inputStage: 
               { stage: 'FETCH',
                 inputStage: 
                  { stage: 'OR',
                    inputStages: 
                     [ { stage: 'IXSCAN',
                         keyPattern: { merchantId: 1, _fts: 'text', _ftsx: 1 },
                         indexName: 'merchantId_1_fullName_text_metadata_text',
                         isMultiKey: true,
                         isUnique: false,
                         isSparse: false,
                         isPartial: false,
                         indexVersion: 2,
                         direction: 'backward',
                         indexBounds: {} },
                       { stage: 'IXSCAN',
                         keyPattern: { merchantId: 1, _fts: 'text', _ftsx: 1 },
                         indexName: 'merchantId_1_fullName_text_metadata_text',
                         isMultiKey: true,
                         isUnique: false,
                         isSparse: false,
                         isPartial: false,
                         indexVersion: 2,
                         direction: 'backward',
                         indexBounds: {} },
                       { stage: 'IXSCAN',
                         keyPattern: { merchantId: 1, _fts: 'text', _ftsx: 1 },
                         indexName: 'merchantId_1_fullName_text_metadata_text',
                         isMultiKey: true,
                         isUnique: false,
                         isSparse: false,
                         isPartial: false,
                         indexVersion: 2,
                         direction: 'backward',
                         indexBounds: {} } ] } } } } },
     rejectedPlans: [] },
  serverInfo: 
   { version: '4.4.5' },
  ok: 1 }
0 Answers
Related