We are trying to use MongoDB to store one of our data. This data contains unstructured JSON and each key can have value of integer, string, array, etc as the value. We want to implement a search feature so we can search for any substring and it will return all data containing that substring from any value.
System configuration: 8CPU, 32GB RAM
Collection stats:
{
"size": 16378755176, # 16379MB
"count": 17060303,
"avgObjSize": 960,
"storageSize": 3441475584,
"indexSizes": {
"$**_text": 4494479360, # 4494MB
…
}
Data example:
{
“user”: {
“id”: 1,
“name”: “Lorem Ipsum”
}
},
{
“file”: {
“id”: 1,
“name”: “lorem_ipsum.txt
}
}
Searching for “lorem”, “ore”, etc should return all these data.
We are using full text index on all values to support this.
{“$**”: “text”}
The issue is the performance is very bad, especially on some cases. For example:
Text Search | Time Taken
“lorem.ipsum@company.com” | 3 minutes
“lorem.ipsum@company” | 5 seconds
“ipsum@company.com” | 3 minutes
“lorem.ipsum@company.org” | 7 seconds
From this we are thinking that maybe “com” is a very common word in our data, so the query is slow. But we are searching the text phrase as one phrase (we quote the text search at the beginning and end), not per terms. It should search one phrase, but it looks like from explain query, MongoDB will split into terms because full text index will index by terms instead (even though the result is still for one phrase). The special characters will always be considered as delimiter.
For regex search, we can’t use any index because we don’t want to match only from beginning of string, but it can be in any part of string.
Is there a way to do this kind of search with better performance?
It is also stated in https://www.mongodb.com/docs/manual/core/link-text-indexes/ that full text index in MongoDB is a legacy feature. Does that mean that it is not recommended to use full text index in MongoDB? Is there any other way to do this?