如何将mysql表拆分为多个表并查询正确的区域？

Question

Below is my friend table,
I included 2 entries to show how it works, When a user adds a person as a friend it inserts 2 entries into the DB with this code;

<?PHP

 //status 0=approved 1=declined approval 3=pending approval
$sql = "insert into friend_friend (userid,friendid,status,submit_date) 
    values
    ('$_SESSION[auto_id]','$friendid','0',now()), 
    ('$friendid','$_SESSION[auto_id]','3',now())"; //Line above is my user ID, the other users ID, status 0 for approved on my side, date
                                                    //next entry is the receiving users entry, there ID, my ID, 3 for not approved yet, date
executeQuery($sql);

//So that code above is my php that adds a friend

//Below is my table scheme for the friends table
CREATE TABLE IF NOT EXISTS `friend_friend` (
  `autoid` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(10) DEFAULT NULL,
  `friendid` int(10) DEFAULT NULL,
  `status` enum('1','0','3') NOT NULL DEFAULT '0',
  `submit_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `alert_message` enum('yes','no') NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`autoid`),
  KEY `userid` (`userid`),
  KEY `friendid` (`friendid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1756421 ;

--
-- Dumping data for table `friend_friend`
--
INSERT INTO `friend_friend` (`autoid`, `userid`, `friendid`, `status`, `submit_date`, `alert_message`) VALUES
(637229, 2, 1, '1', '2007-10-18 01:02:00', 'no');
INSERT INTO `friend_friend` (`autoid`, `userid`, `friendid`, `status`, `submit_date`, `alert_message`) VALUES
(637230, 1, 2, '1', '2007-10-18 01:02:00', 'no');

INSERT INTO `friend_friend` (`autoid`, `userid`, `friendid`, `status`, `submit_date`, `alert_message`) VALUES
(637231, 22901, 1, '1', '2007-10-18 02:24:05', 'no');
INSERT INTO `friend_friend` (`autoid`, `userid`, `friendid`, `status`, `submit_date`, `alert_message`) VALUES
(637232, 1, 22901, '1', '2007-10-18 02:24:05', 'no');
?>  

What I am wanting to do is split the friend_friend table up into multiple tables based on user ID number
Like all user ID's between 1-20,000 go to one table, all userIDs 20,001-40,000, 40,001-60,000 all go to a different table

I am not sure how to do this best, I would need to detect which table a user should query when adding a new friend and well as when retrieving friend list of users
I assume in my code at the top, the 2 entries to add a user would have to be broken into 2 queries and update different tables probably?

Answer 1

Assuming you are using MySQL 5.1 or above, then you can use partitioning to do what you want. See the following links:

http://dev.mysql.com/doc/refman/5.1/en/partitioning.html

http://dev.mysql.com/tech-resources/articles/performance-partitioning.html