Efficient indexing of an emails collection for ordering & filtering by email domain

I'm using Mongoose to hold a central collection of email addresses, and I also have collections for Users and Organisations. In my app I associate Users with Organisations through their (verified) email domains. E.g. Acme Ltd owns the domains acme.com and acme.co.uk, and by selecting from all emails using those domains, I can collate a unique list of associated users.

Users can have many email addresses (1 primary + numerous secondary emails). Users can't share email addresses (hence the "verifiedBy" field which enforces a one-to-one relationship between Users and Emails).

My schema is (currently) as follows:

const emailSchema = new Schema({
    _id: { 
        type: String,
        get: function idReverse(_id) { if(_id) return _id.split("@").reverse().join("@"); },
        set: (str) => { str.trim().toLowerCase().split("@").reverse().join("@") }
    },
    verifiedBy: { type: String, ref: 'User' }
}, options );

> My question is whether it is worth reversing the domain parts of the
> email address in the setter, and unreversing them in the getter - as
> I've shown - in order that the underlying MongoDb index on _id can improve
> performance & make it easier to deal with the kinds of lookups I've
> described?

The alternatives I've already considered are:

• Storing the email as is and using regex to select users by domain part (feels expensive to me processing-wise)
• Storing the domain part in a separate field and indexing that (feels expensive as there'd be two indexes, and duplicated data storage)

The first options should actually work pretty well. According to the $regex docs:

> [...] Further optimization can occur if the regular expression is a “prefix expression”, which means that all potential matches start with the same string. [...]
>
> A regular expression is a “prefix expression” if it starts with a caret (^) or a left anchor (\A), followed by a string of simple symbols. [...]

<br>

Experiment

Let's check how it works on a collection with ~800k docs, ~25% of them have an email. The analyzed example query is {email: /^gmail/}.

Without an index:

db.users.find({email: /^gmail/}).explain('executionStats').executionStats
// ...
//    "nReturned" : 2208,
//    "executionTimeMillis" : 250,
//    "totalKeysExamined" : 0,
//    "totalDocsExamined" : 202720,
// ...

With a {email: 1} index:

db.users.find({email: /^gmail/}).explain('executionStats').executionStats
// ...
//    "nReturned" : 2208,
//    "executionTimeMillis" : 5,
//    "totalKeysExamined" : 2209,
//    "totalDocsExamined" : 2208,
// ...

As we see, it definitely helps - both in terms of execution time and examined docs (more examined docs means possibly more IO work). Let's see how it works if we'll ignore the prefix and use the query more directly: {email: /gmail/}.

Without an index:

db.users.find({email: /gmail/}).explain('executionStats').executionStats
// ...
//    "nReturned" : 2217,
//    "executionTimeMillis" : 327,
//    "totalKeysExamined" : 0,
//    "totalDocsExamined" : 202720,
// ...

With a {email: 1} index:

db.users.find({email: /gmail/}).explain('executionStats').executionStats
// ...
//    "nReturned" : 2217,
//    "executionTimeMillis" : 210,
//    "totalKeysExamined" : 200616,
//    "totalDocsExamined" : 2217,
// ...

In the end, the index helps a lot, especially when performing a prefixed query. It looks like the prefixed query is fast enough to keep it as it is, in a single field. A separate field may utilize the index even better (play with it!), but 5ms is good enough, I think.

As always, I'd strongly encourage you to perform tests on your data and see how it performs, as the data characteristic may impact the performance.