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.

What do your queries and indexes look like? Are you grabbing the 25 records you need at the same time, or slow-by-slow?

I've found the answer. Thanks anyway

There really isn't a single answer.