Jump to content

Cluster a table


jaymc

Recommended Posts

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

Link to comment
https://forums.phpfreaks.com/topic/63186-cluster-a-table/
Share on other sites


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 

Link to comment
https://forums.phpfreaks.com/topic/63186-cluster-a-table/#findComment-320777
Share on other sites

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.  :D

Link to comment
https://forums.phpfreaks.com/topic/63186-cluster-a-table/#findComment-320799
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.