freemail | ruby implementation the npm 'freemail ' database | Runtime Evironment library

I have a table with marketing email events, where an event can be that we sent the email, and then that the recipient clicked it or opened it. Each click and open is an event. We have 2 years of data which is around 150 million rows.

I derive the email domain from each recipient email address, and I recently put an index on that domain column. This improved the performance significantly, and now I include both the domain and the address in a filter. What took 10 minutes, takes less than 10 seconds.

At least, that is true for most domains. There are 7.4 million unique email addresses, and almost half of those are GMAIL. If I query for a gmail address it is very slow, perhaps even slower than without the index. At least the top 50 most common domains take at least one minute for a basic query. The next most common domain has 400k email addresses.

Most of these are what we call "Freemail" domains and I have a long list of potential freemail and otherwise non-private domains (in addition to common email providers like gmail, it also includes "disposable" domains that users sign up for)-- this list is over 5000.

I have two questions/goals. First, would be to simply come up with a way to make querying Gmail faster (and I could selectively apply this to other most common domains). I have ideas, that may be bad, like creating a column with the first letter of the email address and making an index on that. (I am not a DBA, I had never created an index on SQL server before so don't assume I've already thought of something obvious).

The second goal would be to more generally use this for anything that is in the table of freemail domains. Most don't need it, but it ensures that whatever strategy stays up to date because that freemail table gets updated both in some automatic ways and by manually adding domains.

An example query and goal.

Goal: Summarize email engagement of a particular user over time