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.3M😎 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.

[Piranha]

I would say that one table with 250,000 records is not that big. Go for one table, it is defenetley the way to go. RDBMS are done to handle these kind of things, so don't worry about it.

[leatherback]

Agree with Piranha.

The NUMBER OF RECORDS is not so much an issue, as long as they are properly organized.. It is the number of REQUESTS that are made that will make the bulk of trouble, and of course, how you go about querying the database. You might want to post some of the things you do to the database, to verify that you are optimizing your database / querying.

[dcace]

Below is the code I use to display the 5x5 view players get of the sectors. The $SESSION['SectorY'] & $SESSION['SectorX'] are the coordinates that they are currently in and i've added sector_galaxy_id to show how I would search the db if I only used 1 table. I've also removed the html from the code.

    $row_start = $_SESSION['SectorY'] - 2;
    $col_start = $_SESSION['SectorX'] - 2;
    $row_stop = $_SESSION['SectorY'] + 3;
    $col_stop = $_SESSION['SectorX'] + 3;
    for ($row_current = $row_start; $row_current < $row_stop; $row_current++) {
      for ($col_current = $col_start; $col_current < $col_stop; $col_current++) {
        $sql = "SELECT * FROM db_sector WHERE sector_galaxy_id = '" . $_SESSION['PlayerGalaxyId'] . "' AND sector_x = '" . $col_current . "' AND sector_y = '" . $row_current . "' LIMIT 0,1";
        $result = mysql_query($sql);
        $num = mysql_num_rows($result);
        if ($num == 1) {
          $sector_id = mysql_result($result, "0", "sector_id");

[leatherback]

This exactly what NOT to do ;-)

Do something like this:

 $row_start = $_SESSION['SectorY'] - 2;
 $col_start = $_SESSION['SectorX'] - 2;
 $row_stop = $_SESSION['SectorY'] + 3;
 $col_stop = $_SESSION['SectorX'] + 3;


$sql = "
SELECT * 
FROM db_sector 
WHERE sector_galaxy_id = '" . $_SESSION['PlayerGalaxyId'] . "' 
AND sector_x <= $col_stop 
AND Sector_x >= $col_start 
AND Sector_y <= $row_stop 
AND sector_y >= $row_start";

$result = mysql_query($sql);
$num = mysql_num_rows($result);
if($num == 25) 
  {
  // Do whatever. In one query (instead of 25 !) you now have all the tiles you need.
  }

[dcace]

So you'd then put a FOR inside that to get the sector info.

$result = mysql_query($sql); 
$num = mysql_num_rows($result); 
if($num == 25) 
  { 
    for ($i = 0; $i < $num; $i++) {
      $sector_id = mysql_result($result, $i, "sector_id");

}
  }

How would this handle a record that doesn't exist. For example if the player is in the top left corner there would be no $row_start = $SESSION['SectorY'] - 2 OR $col_start = $SESSION['SectorX'] - 2 as they would be negative numbers. The db only stores records with X 1 to 100 and Y 1 to 100 (total of 10,000). Currently how I have it now I just use ELSE like:

       $num = mysql_num_rows($result);
        if ($num == 1) {
          $sector_id = mysql_result($result, "0", "sector_id");
        } else {
          // SHOW SECTOR DOES NOT EXIST
        }

Basically I'm asking how do I handle a record that doesn't exist inside a FOR and will the QUERY be able to complete if the record doesn't exist?

[leatherback]

Hi,
I would personally do something like:

if($num == 25) // you do not NEED 25 do you?
  { 
  while($row = mysql_fetch_array($result))
    {
    $sectorid = $row['sectorid'];
    // and grab whatever else you want from here. 
    You could do something like:
    $sectord[$sectorid]['gold'] = $row['no_gold_units'];
    }
  }

For the borders:
something like:

if($row_start < 0)
  {
  $row_end = $row_end + abs($row_start)
  $row_start = 0;
  }
// same for cols

Or you can decide you do not care: It will retrieve the data fine, it will just return fewer rows. When you are looping through the results, you could fill a 5*5 array with all the data you need, and just keen in mind the right locations. Some places in the sector of teh payer will just not be filled.

[dcace]

I've got it working. Thanks for the help.