I've got an interesting problem for you all. I'd love to hear what you think.

I've simplified the database design of my web application to its root problem, so it'll be very easy to see my difficulty, and if you're feeling generous possibly offer a solution.

I have two tables.

member table (circa 1,000,000 rows - 100 meg - easily fit in a single table)
username varchar(30) pk
password varchar(30)
settings varchar(30)

member_log table (circa 3,000,000,000 rows - a few 100 gigs - a few 100 very simple updates and selects a second, with some quite long selects every minute or so - when the update is being done it needs to select the settings for the user from the member table before it does the update to the member_log table)

logid medint pk
fk_username varchar(30) fk
description varchar(200)

Now ehm what's the most efficient way of doing this?

What I would like to do is:

Is have a copy of the member table on every server, then break up the member_log based on the username, and spread it across multiple servers.

database server a

full member table
1/4 member_log table

database server b

full member table
1/4 member_log table

database server c

full member table
1/4 member_log table

database server d

full member table
1/4 member_log table

In the future, if the servers start to slow down then I'll just add

database server e

full member table
member_log table

Well that's what I'd like to do, but I don't know how to do this.

My main problem is keeping the full member table in sync.

I can't use replication, because I only want to keep one table in sync, not the whole database.

So i don't know what to do. How do I do this, and do this efficently?