Effective storage of Email Address into Database System

Problem :

Email Address is a very common field and column for each and every application or system. As per application size and scope relevant database system store lots of user Email Addresses.

E.g. 10,00,000 email address is stored in one database system and this is also increasing day by day. Usually any database user stores all this email address into one column. Most probably there is one column name is EmailAddress Varchar(250)  and user store email address like ” myprofile@yahoo.co.in ” into this column.

This is great and usually 70% to 75% user doing this.

Once database size and number of users go to increase day by day at that time this kind of email address storage will not perform as per expectation and decrease the performance of the database.

How this will decrease the performance of the database?

  • As we assumed that email address stored into one column like ” myprofile@yahoo.co.in”.
  • Now your CEO is required to find that, how many total number of users from only ” Yahoo ”  domain?
  • How many total number of users from only ” India Yahoo “?
  • Require to set some additional offer only for ” Gmail ” domain.

Friends, now think to all above question.

In my career path I have also stored email address into one column, but gradually I came to know that this is not good to store into one column because now a day Email address is not only for user name or only for storage purpose. This is very important for promotional and marketing activity for any application.

When you have stored into one column and let’s imagine if you want to find only ” Gmail ” domain out of 10,0,00,000 email address. As database developer, you can think that you require one string operation on all stored email address in which you will find the part which is after ” @ “. But this is not a solution where string searching and parsing is required, which slow down the performance of relevant select statement.

Report possibility is an N number of but at least this kind of basic requirement of report we can manage by designing  a proper structure to store Email Address.

Solution :

The solution is simple,

Just divide your Email address into two part or three part.


  • Single Part : ” myprofile@yahoo.co.in “.
  • Two Part : ” myprofile” and ” yahoo.co.in “.
  • Three Part : “myprofile” and ” yahoo ” and ” co.in “.

Now I am going to explain this using one MySQL Demo. 


I have created above two table into MySQL. I have divided Email Address into three part.

Below is a sample data.

tbl_User table Data:

User Mastertbl_Email table Data:

Email Master

Examine the first email address:

User Anvesh has anvesh@gmail.com, Roy has roy@yahoo.co.in

As you can find the EmailDomainID reference column in, a User master table which is indicating that which email address is associated with User.

Now thing is going to easy:

How ?

If I require to find how many users with gmail then I require to find only EmailDomainID=1.

You can also apply easy extension filter like yahoo from co.in and .com.

This will give you 80% to 85% better performance than to store Email into one column.

Also, This will reduce the size of data, E.g, if we store ” Gmail ” keyword 10,00,000 times into the database, then it consume more space than to store only integer ” 1 “.

Yes, I also agree that, during every insert you have to split email address for storing purpose, but this is good instead of  parsing string to count only domain name.

This structure is quite complex to manage at every insert and at every email existence check but it gives better performance.

Below is simple string operation.

Full Email Address is : anvesh@yahoo.co.in

Now parse only ” anvesh ” from anvesh@yahoo.co.in

Now parse only ” yahoo ” from anvesh@yahoo.co.in

Now parse only ” co.in ” from anvesh@yahoo.co.in

The above code is required whenever you insert the email address into the database because from the application end we receive one full email address, but for effective storage you have to parse like above code. You can also suggest three different input parameters from the application where above all code can shift to at application level.

This code is only for MySQL.

Click here to find details about string functions of MySQL.

I hope this post will help you to store Email Address in Effective way.

Anvesh Patel

Leave a Reply

7 Comments on "Effective storage of Email Address into Database System"

Notify of
Sort by:   newest | oldest | most voted

Nice Article in detail… keep it up…


I liked this concept, which is as per boy codds rule. This concept is useful for reporting servers or warehouses. But if iam using login for authentucation of my website, imagine pain of splitting it and storing it. Also the pain of joins while authenticating user.


This solution will not fit in OLTP world. If you have thousands on users connecting to server and trying to join email domain table, it leads to lock contention.