mongoDB Regex Scan Optimization - Suffix based?

Question

Okay, so I have a collection full of folks' emails. I want to efficiently look up the domain of the email without altering the existing data.

Currently I can look up the username SUPER fast since it's a regex scan that is prefix based, my collection is about 1GB+ in size and my server isn't super powerful. I do have an index on "Email". The fast query is something like:

db.emails.find({"Email": {'$regex':'^johnsmith'}})

My index is simple and looks like this: db.emails.createIndex({ Email: 1 })

However, if I try to find the domain, I have to use a non-prefixed regex scan like this, but this results in a query that still uses the index, but takes about 10-20x more time:

db.emails.find({"Email": {'$regex':'sampledomain.com'}})

I've tried using a suffix instead of a prefix like this, but still just as slow:

db.emails.find({"Email": {'$regex':'sampledomain.com&'}})

I'm not sure if there's some sort of index I can make just on the domain portion of the email, but I'm pretty new to mongoDB so any advice would be appreciated.

Hmmm... is that different than a normal index? let me try it :D — SerialEnabler
– SerialEnabler, Commented Jun 18, 2020 at 14:00
So I looked it up and it sounds like text indexes are better for sentences/words, but a regular index is better for emails/usernames, so not sure it would help? Let me know if I'm wrong though. "A text index on the other hand will tokenize and stem the content of the field. So it will break the string into individual words or tokens, and will further reduce them to their stems so that variants of the same word will match ("talk" matching "talks", "talked" and "talking" for example, as "talk" is a stem of all three). Mostly useful for true text (sentences, paragraphs, etc)." — SerialEnabler
– SerialEnabler, Commented Jun 18, 2020 at 14:02
I think text index will perform better here but it won't find stuff johnsmith = johnsmithjr for example where regex would. — Tom Slabbaert
– Tom Slabbaert, Commented Jun 18, 2020 at 14:05
I will test it and see if it's better of an option. I do kind of need the wildcard for the domains/usernames though so hopefully there is another option that is more efficient with regex! — SerialEnabler
– SerialEnabler, Commented Jun 18, 2020 at 14:07

D. SM · Accepted Answer · 2020-06-18 15:32:02Z

1

If you are searching from the beginning of domain, you can extract the domain and store it in another field, at which point you'd be able to use prefix regexp match on that field.

answered Jun 18, 2020 at 15:32

D. SM

14.6k3 gold badges15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mongoDB Regex Scan Optimization - Suffix based?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related