Multiple tables

[jasondavis]

Is it better to have 1 really large table or break into smaller tables? If it is into smaller tables I have the code below that looks at this table could someone show me the best way to select from multiple tables with this code?

Also im assuming if I added to another table I would just change my code that inserts into current table to insert into new table once this current table gets as large as I want to allow it?

Right now Im at around 300,000 wors for this table and it is only 15 mb but eventually this table will be millions of rows

   $sql="select * from friend_friend where userid='$info[auto_id]' and  friendid='$friendid'";
   $result=executeQuery($sql);

function findfriend(){
   $temp=$_SESSION['info'];
   $sql_friend="select userid,friendid,status from friend_friend where userid='$temp[auto_id]' and status='1'";
   $result_friend=executeQuery($sql_friend);
   while($line_friend=mysql_fetch_array($result_friend)){ 
   $friend[]=$line_friend['friendid'];
   }
  $cnt=count($friend);
   if($cnt>0)
	  { 
	 	$str_friends_ids=implode(",",$friend);
  } 
  else
	   { 
  $str_friends_ids='';
  }
   return $str_friends_ids;
}

Here is the table structure

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',
  `birthday_alert` enum('1','2','3') NOT NULL default '1',
  PRIMARY KEY  (`autoid`),
  KEY `userid` (`userid`),
  KEY `friendid` (`friendid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=418693 ;

[jasondavis]

Would this work well?

sql_friend="select userid,friendid,status from friend_friend,friend_friend2,friend_friend3,friend_friend4,friend_friend5,friend_friend6 where userid='$temp[auto_id]' and status='1'";

[cgraz]

Anytime you design at able with fields like this:

id, somefield, item1, item2, item3, item4, item5, etc

there is an IMMEDIATE problem. What happens if you need to add an item 6, 7, and 8? How could you efficiently determine how many items each user has? In this case, the best bet is to do:

main table
MainID, ThisField, ThatField

Items table
ItemID, Item

main_items table
MainID, ItemID

Notice in the main_items table, you have two foreign keys: MainID from the main table, and ItemID from the items table. This is a much more efficient design.

do a search on google for "database normalization" and read up on a few articles. Give your table structure another try, and post what you've got here.

BTW, 1 large table or multiple tables always depends. A general rule of thumb is that each table should only have on subject. So if you have multiple subjects in one table, you should generally split it up into multiple tables.

and millions of records in MySQL is no big deal. It was designed to handle millions upon millions of records. What's important is to have a normalized, properly indexed table and efficient queries!

Hope this helps.