Re: DB Design of GMail: Multiple tables vs. one table?

On 2005-04-03, joeserel_at_gmail.com <joeserel_at_gmail.com> wrote:
> I guess what I am wondering is just a simple question:

Your question is not simple at all.

> Say if a mail server is supporting 2000 accounts. Each account has
> 200M storage quota. Suppose after several years each account will have
> several thousands (say 5000) emails. If we keep all the emails in one
> table (using userID to distinguish), we'll have a table with 10 million
> rows. If we keep the emails in seperate tables, we'll have 2000 tables
> with 5000 rows for each.
> Which one is better? From what I gather, probably one table is better

Neither. You are missing fundamental questions prior to that. _How_ are you going to access these emails? Directly, via POP3, via IMAP, another protocol? _How_ are you going to _index_ these mailboxes? For every header, for some? If you are going to support IMAP, how are you going to support its search capabilities?

> to manage - but will that cause performance problem? Say if this is a
> web mail system, whenever a user login and access Inbox, you'll have to

Webmail systems suffer more because of the needed interoperability between HTTP and POP3 (or IMAP). That is why projects like imapproxy exist. You can read more on comp.mail.imap

> load first page of emails from that 10-million-row table. Will the
> performance be hurt even with proper indexing?

Is this a homework you are doing? Why are you so focused on those two options? There are other problems to consider: What if the same email body is directed to N users of the system? Are you going to save it N times or one?

-- 
#include <std/disclaimer.h>