On Monday 07 June 2004 03:08 pm, Rick Widmer wrote:
> Jeremy Kitchen wrote:
> > On Monday 07 June 2004 02:22 pm, Jeremy Kitchen wrote:
> >>>As an ex-database admin, I wrote the many-domains=no option as
> >>>an optimization to the database schema to save disk space.
> >>>
> >>>Consider hosting one domain with 1 million email accounts.
> >>>The pw_domain(64 char) field is redundant (only one domain). So
> >>>we would be wasting 64MB of space in the database. So instead
> >>>the table is named after the domain.
> >>
> >>I was about to ask you this but you ran off before I could.. so I'll just
> >>post for discussion purposes.
> >>
> >>Isn't this what the varchar field is for?
>
> Yes it is, but the current database layout uses char fields.  Still if
> you have one domain with 1 million accounts, you have at least 4MB of
> duplicated domain names, if the domain name is a.au, better than the
> 64MB the current char field would use, but not as good as 0 bytes if the
> table is named for the domain and you don't store the domain name at
> all.  It is interesting in theory, but there probably aren't many sites
> like this.

ahh good point.  My thought is though, if you have a million users, 4 megs of 
extra 'wasted' space (even 64 megs) is trivial.  I think people with a 
million users are more concerned about speed than fretting over a few megs of 
lost space :)

> It does bring up an interesting question...  would it be a good idea to
> change from char to varchar for all the fields?  It should give a
> substantial reduction in database size, and I don't think it will be
> that much slower.  Someone _should_ be able to do an alter table and
> find out if anything breaks...

yea, I don't think that would break anything.  Unfortunately I don't have a 
machine to try that on (other than a few production servers but I'm sure 
they'd shoot me if I broke them *grins*)

> p.s.  It does bring up a real question - does anyone actually use
> separate tables for each domain anymore?  It would be nice if we could
> remove the option some day.  (It causes a number of four state ifdef
> structures that are kind of messy.)

I know when I set up mysql backends I leave it at the default, I would assume 
that most people also do (unless they really know what they're doing, in 
which case they probably need the single table anyways for performance 
reasons)

-Jeremy

-- 
Jeremy Kitchen ++ Systems Administrator ++ Inter7 Internet Technologies, Inc.
  [EMAIL PROTECTED] ++ www.inter7.com ++ 866.528.3530 ++ 847.492.0470 int'l
        kitchen @ #qmail #gentoo on EFnet ++ scriptkitchen.com/qmail

Reply via email to