Large Table Vs Multiple Tables

[dcace]

I've very new to mysql but have used it with php to create an online browser based space game. I'm about to expand by game but don't know the best way to use the database. Currently there are 10,000 records in a table called db_sectors (1.3MB) which represents all of the sectors of space in the game. This table is access heavily by the game as each nearly each page the player views shows a 5x5 grid of the galaxy so 25 records have to be accessed. I want to make the game bigger by going from 1 galaxy of 10,000 sectors to 25 galaxies of 10,000 sectors making a total of 250,000 sectors.

 

Should I have all the sectors in one table with a galaxy_id field to represent which galaxy the sector is from like:

 

db_sectors

 

db_sectors.sectors_id

db_sectors.sectors_galaxy_id

db_sectors.sectors_x

db_sectors.sectors_y

 

or have 25 tables like:

 

db_sectors1

 

db_sectors1.sectors_id

db_sectors1.sectors_x

db_sectors1.sectors_y

 

db_sectors2

 

db_sectors2.sectors_id

db_sectors2.sectors_x

db_sectors2.sectors_y

 

I'm worried that having 250,000 records in one table will slow the game down as the database will not be able to access the records as fast as it would with 25 seperate tables. I know very little about how mysql works and have gotten by so far with basic tutorials and lots of trial and error. Any help would be appreciated.

[fenway]

250K records isn't really that many, provided the table is indexed properly -- also, there is an opens tables "limit", so if you're going to be accessing them all anyway, there's no advantage to keeping them separate, IMHO.

[printf]

250,000 records isn't a lot of records with you use indexing properly. You can also do what your wanting to do as long as you keep your relationships. What I mean is that a user has the ability to cross over into different sectors by your application doing some type of join. Many times people have their own reasons for using many tables, like say you have a city database and each city has a state_name, you could list the state_name column in the cities table, but to save space, you can also create a states table and put your state_name in their, then assign the cities table a tinyint(2) column that has a relationship with the primary key in the states table and use a join to get the state_name. If you 50,000 records you just saved your self a bunch of space, without hurting your application processing time.

 

But for being faster or less intense, when using more tables, I don't see it as being beneficial in your case because overall all the tables would be the same, the only difference being the sector!

 

printf