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 }