Opinion: Most efficient mass email storing strategy

[chasethemetal]

Hey all, I am about to implement a mailing list system I built and one thing I can't decide on is how I am going to store the emails. The reason it's not so simple is this system needs to be scalable, as in 50,000 + users, all of which might have 20,000 + emails and in ONE table. The two ways I figure to do this are... Having a user id column and an email column, every time a user ad's an email to there list we create a new row with there user id and the new email. The issue with this, is this table could potentially get way to huge with millions of records and take too long to pull from and update from. The second way which i'm leaning towards is to have again a user id column and an email column, this time the user id column is unique and I instead store all of the emails in the email column like "email1@gmail.com,email2@gmail.com". perhaps even in a json format too... And when I need to add one, I would just append a new one to the end. Now the problem with this one is the email field could also get huge with 20,000 + emails, and it might take a while to pull them all out and append a new one to the end... Which way do you think would be more scalable when it comes to storing emails? Also, what table type might be more efficient, InnoDB or MyISAM? Thanks so much!

[justsomeguy]

InnoDB would be more scalable, and the only reason you would even consider using the second type is if you aren't searching for specific email addresses. I assume you're talking about email addresses, not actual emails, right? If you're just looking up all of the addresses for a specific user then the second one would work, if you need to do something like finding all users who have a certain address then the second is not a good idea. I would recommend the first suggestion, even if you're not searching for addresses.

The issue with this, is this table could potentially get way to huge with millions of records and take too long to pull from and update from.

How many records are too many, or how long is too long?

[chasethemetal]

Thanks! How many records are too many / too long? These are things I couldn't give you an answer too, what do you think, whats the best practice? Would it be ridiculus to have 200 million rows in a table? I won't ever be searching for specific emails, and if I needed to I have functions to pull the email feild out, explode it and search the foreach loop for the speciifc information... I guess my question is if I did it method 1, would it be ridiculus to have 200 million + rows?

[justsomeguy]

It's certainly not unheard of to have that many, I've got some installations that have tables with 30 or 40 million records. But if that's what you need then that's what you need, the ways to speed up performance in that case involve things like high-end hardware in a dedicated database server, or database clusters. If this was in a shared hosting environment, for example, you may not see very good performance on the database.

[chasethemetal]

Thanks! Right now I'm running on a dedicated centos. In conclusion, It's not necisarly your database design at this type of scale, it has a lot to do with your infustructure. I am assuming that if I hit 200 million + records we will be at a point where we have our own dedicated database server(s). Alright I think I'm going to go with method 1, as I don't want to use PHP to sort information, I think that might be more taxing.

[justsomeguy]

Database design definitely matters, things like appropriate indexes or choosing between InnoDB and MyISAM can make a difference like night and day, but if you have the "perfect" database structure for your situation then really the only way to scale from there is more hardware.