jaymc Posted August 3, 2007 Share Posted August 3, 2007 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 More sharing options...
jaymc Posted August 6, 2007 Author Share Posted August 6, 2007 No feedback guys? Link to comment https://forums.phpfreaks.com/topic/63186-cluster-a-table/#findComment-317005 Share on other sites More sharing options...
fenway Posted August 10, 2007 Share Posted August 10, 2007 We'll need more about about "heavily accessed", read/write, frequency, etc. Link to comment https://forums.phpfreaks.com/topic/63186-cluster-a-table/#findComment-320241 Share on other sites More sharing options...
teng84 Posted August 10, 2007 Share Posted August 10, 2007 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 More sharing options...
roopurt18 Posted August 10, 2007 Share Posted August 10, 2007 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. Link to comment https://forums.phpfreaks.com/topic/63186-cluster-a-table/#findComment-320799 Share on other sites More sharing options...
jaymc Posted August 16, 2007 Author Share Posted August 16, 2007 We'll need more about about "heavily accessed", read/write, frequency, etc. How can I find that out? Link to comment https://forums.phpfreaks.com/topic/63186-cluster-a-table/#findComment-326232 Share on other sites More sharing options...
fenway Posted August 21, 2007 Share Posted August 21, 2007 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? Link to comment https://forums.phpfreaks.com/topic/63186-cluster-a-table/#findComment-330388 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.