> Well, then please help me find a better design cause I can't see one...
> what we have here is a big "membership" table of email lists. When
> there's a sendout then the memberships of the affected group are heavily
> read/updated, otherwise they are idle. None of the memberships is
> archive data, they are all active data... the only problem is that they
> are so many. Is it so hard to believe that >100 million rows is all
> active data, but only used in bursts once per week (that's an example,
> some groups are more active, others less) ?
I suppose you have a table memberships (user_id, group_id) or something
like it ; it should have as few columns as possible ; then try regularly
clustering on group_id (maybe once a week) so that all the records for a
particular group are close together. Getting the members of a group to
send them an email should be faster (less random seeks).
For tables with very few small fields (like a few integers) the
26-something bytes row overhead is significant ; MySQL can be faster
because MyISAM tables have no transaction support and thus have very
little things to store besides actual row data, and the table can then fit
in RAM...
