Slow Search by nested fields

MongoDB Atlas
, , ,
1

Hi, there!
We have a product and faced a problem with slow searching.
Here the details:

  1. We have “room” collection(over 4 mln documents only on staging server, production - much more)
    which model has field “keys” which is used for searching
  2. Field “keys” has several nested documents
    Example:
"keys" : {
        "searchPeer" : [ 
            "parampampam2", 
            "parampampam1" 
        ],
        "datacenter" : [ 
            "East-US" 
        ],
        "company" : [ 
            "company-name" 
        ],
        "testId": [...],
        "location": [...],
        "url": [...],

}
  1. When we try to search room there are 7 fields involved in searching, 6 of them are nested.
    And this is (as I understand) the reason why search is so slow
    Example: part of query to mongodb to search by value “shortrundampshiproom1”:
{$or: [
        {name: {$regex: '.*shortrundampshiproom1.*', $options: 'i'}},
        {"keys.qrtcTestId": {$regex: '.*shortrundampshiproom1.*', $options: 'i'}},
        {"keys.searchPeer": {$regex: '.*shortrundampshiproom1.*', $options: 'i'}},
        {"keys.datacenter": {$regex: '.*shortrundampshiproom1.*', $options: 'i'}},
        {"keys.location": {$regex: '.*shortrundampshiproom1.*', $options: 'i'}},
        {"keys.company": {$regex: '.*shortrundampshiproom1.*', $options: 'i'}},
        {"keys.url": {$regex: '.*shortrundampshiproom1.*', $options: 'i'}}        
      ]}

Q: how we can make the search faster?

2

By not abusing $regex with ‘i’ $options.

If this is a frequent use-case you should normalize the data into all lower cases or all upper cases.

3

No, this make response even longer
With option ‘i’ it is about 16-19 seconds, without ‘i’ - 44-49 seconds.