Cluster a table

[jaymc]

I have a huge table with 3,000,000 records in which is heavily accessed

 

I need to do something about it because its causing problems with load

 

I was thinking of taking the username field and moving usernames starting from A-D into an A-D table, usernames starting with E-H to an E-H table, etc etc

 

So I would have maybe 8 tables with 250,000,000 rows in..

 

That would also give me the opportunity to spread the tables onto different servers

 

Thats what ive come up with, is that the best way to go about it when you have a huge heavily accessed table?

 

Feedback would be great

 

Thanks

[jaymc]

No feedback guys?

[fenway]

We'll need more about about "heavily accessed", read/write, frequency, etc.

[teng84]

I was thinking of taking the username field and moving usernames starting from A-D into an A-D table, usernames starting with E-H to an E-H table, etc etc

 

this is good it even better if you do that per letter but take note about the realtionship of the table consider how are you going to join them say if you searc a record on the other table then you will have to match them in your a-z tables 

[roopurt18]

I've never dealt with an issue like this before and have no idea what kind of search capabilities you hope to provide, but if you break them up A-D, E-F, etc. you will have to continuously adjust who goes where to keep the load fairly distributed.

 

A good hashing function can do this for you.  Let's say you want to distribute across N tables:

users0, users1, ..., usersN

 

You create a hash function:

  // returns an int between 0 and N inclusive based on user name
  function username_to_table_hash($user){
    // perform magic
    return $hash;
  }

 

Usage:

  $uname = clean_post_value($_POST["uname"]);
  $tbl = "users" . username_to_table_hash($uname);
  $q = mysql_query( "SELECT * FROM {$tbl} WHERE uname={$uname} LIMIT 1" );

 

Don't know how practical that'd be though; I got the idea from when I wrote a hash table back in college. 

[jaymc]

We'll need more about about "heavily accessed", read/write, frequency, etc.

How can I find that out?

[fenway]

We'll need more about about "heavily accessed", read/write, frequency, etc.

How can I find that out?

You're supposed to know, it's your database, isn't it?