Table too big in MySQL database

Question

Hi. I am making an online game and one table in my database. The most important one maybe. It's starting to become very large. I am creating a function where you can go together with multiple users and for that I have a table that look like this so far:

CREATE TABLE `oc` (
  `id` int(11) NOT NULL auto_increment,
  `leader` varchar(40) NOT NULL default '',

  `car` int(11) NOT NULL default '0',
  `car_type` char(2) NOT NULL default '',  

  `seats` varchar(3) NOT NULL default '0',
  `share` enum('1','2') NOT NULL default '1',
  `location` varchar(100) NOT NULL default '',  

  `user1` varchar(40) NOT NULL default '', 
  `user2` varchar(40) NOT NULL default '',  
  `user3` varchar(40) NOT NULL default '', 
  `user4` varchar(40) NOT NULL default '', 
  `user5` varchar(40) NOT NULL default '',  
  `user6` varchar(40) NOT NULL default '', 
  `user7` varchar(40) NOT NULL default '', 
  `user8` varchar(40) NOT NULL default '', 
  `user9` varchar(40) NOT NULL default '', 

  `leader_gun` char(2) NOT NULL default '', 
  `user1_gun` char(2) NOT NULL default '', 
  `user2_gun` char(2) NOT NULL default '',  
  `user3_gun` char(2) NOT NULL default '', 
  `user4_gun` char(2) NOT NULL default '', 
  `user5_gun` char(2) NOT NULL default '',  
  `user6_gun` char(2) NOT NULL default '',  
  `user7_gun` char(2) NOT NULL default '', 
  `user8_gun` char(2) NOT NULL default '', 
  `user9_gun` char(2) NOT NULL default '',   

  `user1_inv` varchar(40) NOT NULL default '', 
  `user2_inv` varchar(40) NOT NULL default '',  
  `user3_inv` varchar(40) NOT NULL default '', 
  `user4_inv` varchar(40) NOT NULL default '', 
  `user5_inv` varchar(40) NOT NULL default '',  
  `user6_inv` varchar(40) NOT NULL default '',  
  `user7_inv` varchar(40) NOT NULL default '', 
  `user8_inv` varchar(40) NOT NULL default '', 
  `user9_inv` varchar(40) NOT NULL default '',    

  `user1_ready` enum('0','1') NOT NULL default '1', 
  `user2_ready` enum('0','1') NOT NULL default '1',  
  `user3_ready` enum('0','1') NOT NULL default '1', 
  `user4_ready` enum('0','1') NOT NULL default '1',  
  `user5_ready` enum('0','1') NOT NULL default '1', 
  `user6_ready` enum('0','1') NOT NULL default '1',  
  `user7_ready` enum('0','1') NOT NULL default '1', 
  `user8_ready` enum('0','1') NOT NULL default '1',   
  `user9_ready` enum('0','1') NOT NULL default '1',     

  PRIMARY KEY  (`id`)
) TYPE=MyISAM ;

Now I wonder what my limitations are. It would be cool to have up to 30 users that could play together, but then my table will need 3 times as many fields. 30 x user1_gun 30 x user1_inv and 30 x user1_ready.

This db will be used heavily since all users will need all the information in the db when they access the page to list all the others username and such. All users can also change at least 3 values in the table.

Should I limit my table to 9 users or can I make a table for 100 users?? I have no idea. I can some coding, but have never managed large mysql databases and servers before now.

EDIT: OK. Just to be clear. I have a user table with all kinds of info on my users, a table for cars connected to the user table, a table for weapons connected to my user table. This is not my only table with where a maximum of 9 players can play the game! The oc table need to have fields for usernames and guns each user has selected and user that have signaled that they are ready.. I don't think I need Database normalization...

Answer 1

You really need to normalize your database! You're using a relational database, so the first thing you should do is define the relationships within your data.

You have a one-to-many relationship between a game and a player (one game can have many players, but a player can only play one game at a time). So, you should create a game table containing information related specifically to each game (id, leader, car, car_type, seats, share, location). Then create a separate user table which contains all the data specific to a user (id, name, gun, inventory, ready). You can then link the user table to the game table by using foreign keys. So, in addition to the user information already mentioned, the user table should include a field such as game_id, which stores the id of a game from the game table as a foreign key.

You can probably do further normalization by including a gun table, a car table, etc.

Normalization reduces redundant data, improving the efficiency of your database, and reducing data anomalies. Here's a good introduction to the concept: http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html This also goes over joining tables, which you'll need to do when querying a normalized database.

EDIT: In response to your clarification, you may want to look into solutions other than a database. You really shouldn't have a table in a relational database that looks like this. Based on the information you've provided, it seems like all this is doing is keeping track of in-game information, and there may be better solutions for that than a relational database.

If you really want to keep this information in a relational database, it should still be normalized. So, if you already have a separate user table, you either need to expand it to account for the in-game user information, or possibly have an active_users table with information specific to their current game.

Answer 2

Why not go for a two column db: basically a key value score. Since it's an online game, this would make it easy to use memcache for fast access of data.

The two columns would be key and value Value would be a json representation of your data.