Storing Emails in a DB

Benji_ used Ask the Experts™

I am wondering what the best way to store information is.. we have a script that downloads mail from an IMAP server at set intervals.

We need to store  the   To, From, Date/time, Body (HTML),Body (Plain Text), Company ID

I have been browsing the best way whether Mysql / DynamoDB NoSQL kind of solution is best.

What would you guys advise to be a way to store this information for read access.

If so what field types would be the best to use?

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
GanapathiFacets Developer

How will you enter the values?
Having a single table to store these info would be enough. Have the below data types for the fields.

TO and FROM --> VARCHAR 255
Date/Time --> DATETIME
Body Text --> Depends based on the size of the text. TEXT would be fine. It allows 64 KB. If you think body text will be more than that, go for MEDIUMTEXT
Body HTML --> MEDIUMTEXT. This would always occupy more space than the body text
Company ID --> I do not know what data it is. You can go for INT if its a numeric or VARCHAR if its alphanumeric.



Data will be entered via a PHP script on a cronjob.

Body of that data could be above 64k, We will be storing around 150k+ emails in the DB.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Then you can go with MEDIUMTEXT which stores upto 16MB. If you are still thinking about more, go for LONGTEXT then, stores up to 4GB
Most Valuable Expert 2011
Top Expert 2016
Where do the emails "come from" (ie: enter your system)?

With anything as small as 150,000 data elements it doesn't matter what data base you use.  You probably want to choose MySQL -- free and open source.
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

If your intention is to use the database as an archive for the email as well as providing good access to it then you should use database and based on the amount of emails you should consider compression as well. MySQL provides a good ARCHIVE storage engine with good compression however you can't use indexes on the tables with that engine. InnoDB storage engine has also row compression which is good where indexing is possible.

Also if you have IMAP server that uses files for storage like Courier you could look into IMAP server like DBMAIL that uses database like MySQL for storage.

     Tomas Helgi

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial