Compound index with regex search

Working with Data
,
1

Consider the below situation,
I have a collection with approximately 5M data, and I have a compound index with fields A,B,C in the same order

db.collection1.find(
{“$and”: [{“A”: {“$in”: [“Value 1”, “Value 2”]}}, {“B”: {“$in”: [“Value 1”, “Value 2”]}}, {“C”: {“$regex”: “^test.com”}}]},{“_id”:1})

For this query the 3rd field has regex search specifically for “starts with search”

It is picking the index in this case

Now consider the below scenario in which third field has regex search as “contains search”

db.collection1.find(
{“$and”: [{“A”: {“$in”: [“Value 1”, “Value 2”]}}, {“B”: {“$in”: [“Value 1”, “Value 2”]}}, {“C”: {“$regex”: “test.com”}}]},{“_id”:1})

In this scenario, it is not picking the compound index provided on the top
If I run the same query with hint, I am getting the results, but without hint it is not picking the index and performance is very slow

Can someone help me finding out the problem here, thanks in advance

2

Hi Krishna,

The regular expression in the first query is of type “prefix expression”, however the second query has another kind of expression.

Now the prefix expressions are optimisable and use indexes.
As you know, logically an index can help such expressions only. It cannot help in a query for “pattern contains any where in the field” since the index created is for the whole field.

Please see the relevant documentation given below.
Kindly request to help me too to confirm my understanding about it.

Thanks
WeDoTheBest4You

3

Furthermore: Will “Compound Text indexes” be helpful in your case?
Please see the documentation if it finds so.

4

Hi @wedothebest_We_do_the_Best
Thanks for your reply
Yes, I do think that “contains search” is complex but I am not sure if index can be leveraged here.
Because I am able to get the results by providing that compound index with hint, but I am not getting the results without hint
I’ve seen the execution stats as well, this compound index is present in rejected plans and it is just picking some random index each time.

5

I am not able to use Text indexes here, since the text search is fully based on some kind of punctuations
For example if I have the value as “testabc.com”, I am getting the results if I give the search as testabc or com, but I am not getting the results for “abc” which is not suitable for this scenario