The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

MySQL's maximum table size is 8 million terabytes: the operating system and hardware are the bottlenecks. In any case I don't think someone who's struggling with tables is the best person to design a 2 million user email application.

Based on your replies, it would appear that the MessageIndex Table would come under pressure (which is what I would expect). Now what I am asking is there a better way to do it?

I would do it like this:

User Table (index on userid)
userid|login|password

Message Table (index on messsageid)
messageid|message|userid

You can parse message for date, who it's form etc, but that's besides the point.

On a more general point, if there was a 500MB table and a record within it had to be updated, would all 500MB be loaded into RAM? How do updates, inserts, etc work memory wise?

I am assuming that, no, it isn't all loaded into RAM. That could get messy in a matter of seconds if it were. If you want to know how database operations work, I would look into the specific database for details.

Why put the e-mails in a DB anyway... or for that matter, don't think of a single DB server handling all of it.

Split it up into several machines.. Each one handling a percentage of the total users... e.g. DB1 handles users 1 - 500,000, DB2 handles 500,001 - 1 million, etc.

You get the idea -- MySQL can't scale well enough to handle it all on one box (most couldn't probably; that is one reason why clustering/replication was invented)...

But again, store the posts in plaintext files or something... It is a waste to store them in the DB if you don't have to (granted if you wanted to do some weird things with the data from an admin end it would be nicer in the DB I think).

I think he was just using that email thing as an example. But anyway...

But again, store the posts in plaintext files or something... It is a waste to store them in the DB if you don't have to (granted if you wanted to do some weird things with the data from an admin end it would be nicer in the DB I think).

Originally posted by Abstraction I think he was just using that email thing as an example. But anyway...

plaintext files? Wouldn't disk access kill you in this situation?

I wouldn't think so -- that is how sendmail et. al handle mail files, right? That way though you could easily distribute them to many, many machines...

I suppose if you did do the splitting up dealie that I mentioned the message table wouldn't get terribly large (50 million rows perhaps?) so it could work... It would tend to get a bit slow I would think though.. Get 10GB RAM and you could store it in memory!