Best way to run 1000+ querys per page

[joecooper]

Hi,

 

My site http://bittleships.com is a simple online battleships game. Theres a 1088 cell grid where each cell is a mysql database entry containing details wether its been clicked, if theres a boat in the location etc.

 

the site usually loads all the querys in 0.3 seconds, but when 5 or more people are playing this easily hits 1+ seconds.

 

The code that displays the grid is this:

 

  echo("<center><span style='background-color:white;color:red;font-size:12'>$griderrormsg</span></center>");
   }
   echo("<table width='20' border='0' cellspacing='0' cellpadding='0'");
   for($i=1; $i<=32; $i=$i+1){
             echo("<tr>");

             for($j=1; $j<=34; $j=$j+1){
             $c++;
                       $cell = $c;
                       $sql="SELECT * FROM grid WHERE `ID` = $cell";
                       $result=mysql_query($sql);
                       $row = mysql_fetch_array( $result );
                       $clicked = $row['clicked'];

                       if ($clicked == "1"){

                          echo("<td title='$j, $i'><img src='b.gif");

                       }elseif ($clicked == "2"){   //if the ship was hit
                          $hitby = $row['user'];
                          echo("<td title='$j, $i - Hit by $hitby.'><img src='h.gif");
                       }elseif ($clicked == "3"){  //if is chest
                          $hitby = $row['user'];
                          echo("<td title='$j, $i - $prevamount btc won by $hitby.'><img src='chest.gif");
                       }else{

                          echo("<td><a href='index.php?c=$c'><img src='g.gif");

                       }
                       echo("' BORDER=0></td></a>");
             }

             echo("</tr>");
   }
   echo("</table>");
}

 

Is there a better way of running this for it to run quicker?

[PFMaBiSmAd]

Yes, you use one query to retrieve all the data you want in the order that you want it. Then simply and quickly iterate over that data and display it the way you want.

[Psycho]

What he ^^ said.

 

Also, I'm curious why you decided to define the "cells" from 1 to 1088. I would have defined the database records with two fields - one for the x axis and one for the y axis.

[joecooper]

What he ^^ said.

 

Also, I'm curious why you decided to define the "cells" from 1 to 1088. I would have defined the database records with two fields - one for the x axis and one for the y axis.

 

That would mean the same amount of entrys?

 

Im unsure how to run 1 query and parse all the data, is there any example?

[PFMaBiSmAd]

<?php
$query = "SELECT * FROM grid ORDER BY `ID`";
$data=array();
$result = mysql_query($query);
while($row=mysql_fetch_assoc($result)){
	$data[$row['ID']] = $row;
}

echo "<table width='20' border='0' cellspacing='0' cellpadding='0'>";
$c = 0;
for($i=1; $i<=32; $i=$i+1){
	echo "<tr>";
	for($j=1; $j<=34; $j=$j+1){
		$c++;
		$clicked = 0; // default value
		if(isset($data[$c])){
			$clicked = $data[$c]['clicked'];
			$hitby = $data[$c]['user'];
		}

		if ($clicked == "1"){
			echo "<td title='$j, $i'><img src='b.gif' alt=''></td>";
		}elseif ($clicked == "2"){   //if the ship was hit
			echo "<td title='$j, $i - Hit by $hitby.'><img src='h.gif' alt=''></td>";
		}elseif ($clicked == "3"){  //if is chest
			echo "<td title='$j, $i - $prevamount btc won by $hitby.'><img src='chest.gif' alt=''></td>";
		}else{
			// some other value or doesn't exist in the database table
			echo "<td><a href='index.php?c=$c'><img src='g.gif' alt=''></a></td>";
		}
	}
	echo "</tr>\n";
}
echo "</table>";

[joecooper]

Thanks alot PFMaBiSmAd!

 

the loading times are still around 0.3 seconds though. Will have a look at what other code could be causing it.

[PFMaBiSmAd]

Does your table contain all 1088 rows, even if those rows don't actually contain any data?

 

A typical game will use a minority of the positions on the grid. You should only store rows that have been clicked (miss, hit, won.)

[xyph]

Also, by displaying 1088 images, you're sending a TON of data with each request, along with 1088 separate requests to the web server. One for each image.

 

I'd suggest dynamically creating the image with ImageMagick/GD and using an image map to determine where the user has clicked.

 

Serving a 52kb HTML file with every request is not good to mix with traffic, let alone 1088 web server requests.

[PFMaBiSmAd]

There's only a http request for each different image on the page.

[xyph]

Ah, I see that there's only 3 different images. My mistake.

 

Regardless, its a 52kb HTML file and the rest of my post still makes a good point

[Psycho]

What he ^^ said.

 

Also, I'm curious why you decided to define the "cells" from 1 to 1088. I would have defined the database records with two fields - one for the x axis and one for the y axis.

 

That would mean the same amount of entrys?

Yes, but, it makes more logical sense to me to store the records by row/column. That way you can easily associate a grid position with a particular record.

 

As, PFMaBiSmAd stated you should only store the grid positions that are not empty. That is also why storign the records by row/column makes sense. You can create a simple PHP loop to create the grid. As it does so, it checks the db results (from a single query) to see if there is something other than a blank square in each position.

[PFMaBiSmAd]

A dynamically produced GIF image, to replace the grid of images, using GD functions, with mostly the background image (g.gif) and one each of the other three images (b.gif - miss, h.gif - hit, chest.gif - win) results in a ~72KB image.

 

Sample code -

 

<?php
$x = 34; // grid size
$y = 32; // grid size
$img_x = 16; // tile/image size
$img_y = 16; // tile/image size

$width = $x * $img_x;
$height = $y * $img_y;

if(isset($_GET['img'])){
// some test data -
$data[5] = array('clicked'=>1);
$data[6] = array('clicked'=>2);
$data[7] = array('clicked'=>3);

$bg_name = "g.gif";
$miss_name = "b.gif";
$hit_name = "h.gif";
$win_name = "chest.gif";

$im = imagecreatetruecolor($width,$height); // canvas
$bg = imagecreatefromgif($bg_name); // background
$miss = imagecreatefromgif($miss_name);
$hit = imagecreatefromgif($hit_name);
$win = imagecreatefromgif($win_name);

$c = 0;
$dst_x = 0; $dst_y = 0;
for($i=1; $i<=$y; $i=$i+1){
	for($j=1; $j<=$x; $j=$j+1){
		$c++;
		$clicked = 0; // default value
		if(isset($data[$c])){
			$clicked = $data[$c]['clicked'];
		}

		if ($clicked == "1"){
			//echo "<img src='b.gif' alt=''>"; // miss
			imagecopy($im, $miss, $dst_x , $dst_y ,0,0,$img_x,$img_y);
		}elseif ($clicked == "2"){
			//echo "<img src='h.gif' alt=''>"; // hit
			imagecopy($im, $hit, $dst_x , $dst_y ,0,0,$img_x,$img_y);
		}elseif ($clicked == "3"){
			//echo "<img src='chest.gif' alt=''>>"; // win
			imagecopy($im, $win, $dst_x , $dst_y ,0,0,$img_x,$img_y);
		}else{
			// some other value or doesn't exist in the database table
			//echo "<img src='g.gif' alt=''>"; // background
			imagecopy($im, $bg, $dst_x , $dst_y ,0,0,$img_x,$img_y);
		}
	$dst_x += $img_x; // next column
	}
	$dst_x = 0; // reset
	$dst_y += $img_y; // next row
}
header("Content-type: image/gif");
imagegif($im);
} else {
echo "<img src='?img' alt='' width='$width' height='$height'>";
}

[xyph]

Well, the site's down, so I can't build a direct comparison myself - why would you render the image using GIF and not JPEG? GIF is not an ideal format for this type of image.

[Psycho]

GIF is not an ideal format for this type of image.

Why do you say that? In my opinion, a GIF would be a good choice since it will require much less bandwidth. A GIF is a good format for images that are not "full-color" i.e. something like a scenic image or an image of a person that requires thousands/millions of different colors. A GIF allows for up to 256 unique colors and is good for things such as logos or "artwork" which is what I would categorize these images as.

[joecooper]

Ah thanks for all your replys. Assumed i wouldnt get much more on this. I thought about using the image idea, but i have no idea how to create the links! perhaps using a simular method to www.milliondollarhomepage.com with coordinates. but im sure there will me so much more data.

 

the table must have all 1088 rows as the round starts over every hour or so. so at the end of it it will reset itself.

 

I have since moved the site to a VPS which has helped alot with the loading times

[Pandemikk]

I think your current problem lies with running that many queries. There's no need for you to run that many queries. Why don't you store all the grids in one table, then fetch them all in that one query?

[PFMaBiSmAd]

the table must have all 1088 rows as the round starts over every hour or so. so at the end of it it will reset itself.

 

There's no reason for the table to have any rows, except those that have miss, hit, or win data. To reset, you simply drop/recreate an empty table or truncate the existing table.

[xyph]

I stand corrected after testing it.

[Psycho]

the table must have all 1088 rows as the round starts over every hour or so. so at the end of it it will reset itself.

 

No, it does not need 1088 rows. You only need records for the cells/positions that have some status other than empty. I think there are probably four possible statuses: 1) there is no boat at that position and no attack has been made (do not store records for these cells), 2) there is no boat and an attack has been made (i.e. a miss), 3 There is a boat at that position and no attack has been made, and 4) there is a boat at that position and an attack has been made (i.e. a hit).

 

Then at the start of a new round you would delete all the records and only create new records for the positions that have boats. Then on each attack you would create a new record (hit or miss) for the position attacked.

[coupe-r]

You could also try using mysqli instead of mysql?

[Psycho]

Here is some sample code of how you can accomplish this with just storing records that have something other than an unhit, empty cell.

 

Note: I assumed some changes as follows:

 

1) Database records will be stored with a row and column value instead of a value from 1 to 1088.

2) Changed the 'user' column in the database to 'hitby' since that is what it means based on the context you are using it.

3) Changed the link for the empty cells to pass two values (row and column positions)

4) A CSS style property should be added to the style sheet to give the images a 0 border instead of hard coding that for each image (not shown in the code below)

 

NOTE: I don't know where the value for $prevamount comes from, just left it in as you had it

 

<?php
//Query all the records ordered by row/column
//Note only contains cells that are hit and/or contain a ship position
$query = "SELECT row, col, clicked, hitby
          FROM table
          ORDER BY row, col";
$result = mysql_query($query);

//Set column and row counts
$max_rows = 32;
$max_cols = 34;

//Get first record from result set
$cell = mysql_fetch_assoc($result);

//Generate the table
$tableHTML = "<table width='20' border='0' cellspacing='0' cellpadding='0'>\n";
for($row=0; $row<$max_rows; $row++)
{
    $tableHTML .= "<tr>\n";
    for($col=0; $col<$max_cols; $col++)
    {
        //Check if current DB record is for this cell
        if($row==$cell['row'] && $col==$cell['col'])
        {
            //Set values from DB record and get next DB record
            $clicked = $cell['clicked'];
            $hitby   = $cell['hitby'];
            $cell = mysql_fetch_assoc($result);
            //If no more cells avail from DB set row/col to false
            if(!$cell) { $cell['row']=fale; $cell['col']=fale; }
        }
        else
        {
            //Doesn't match current DB record set default status
            $clicked = 0;
        }
        
        //Set image based on cell status
        switch($clicked)
        {
            case 0;
                $tableHTML .= "<td><a href='index.php?r=$row&c=$col'><img src='g.gif'></a></td>\n";
                break;
            case 1;
                $tableHTML .= "<td title='$col, $row'><img src='b.gif'></a></td>\n";
                break;
            case 2;
                $tableHTML .= "<td title='$col, $row - Hit by $hitby.'><img src='h.gif'></a></td>\n";
                break;
            case 3;
                $tableHTML .= "<td title='$col, $row - $prevamount btc won by $hitby.'><img src='chest.gif'></a></td>\n";
                break;
        
        }
        $tableHTML .= "<td></td>\n";
    }
    $tableHTML .= "</tr>\n";
}
$tableHTML .= "</table>\n";
?>

[joecooper]

VERY sorry to revive this 2+ year thread. But I have reopened my site again (different domain).

 

Back facing this problem.

 

The database needs to hold the info for all of the cells as they contain other data such as if a boat is in that square, or a chest.

[mac_gyver]

a) what is your current code and the current issue?

 

b) as stated a number of times, you only need to have rows in the database table that have data values - boat, chest, miss, hit..

 

c) the only time you should be drawing the entire grid is at the start of a game. you should use ajax to update the display during the game play, edit: because you are only concerned with changes that occur in the data due to a guess.

Edited October 23, 2013 by mac_gyver

[mac_gyver]

d) have you profiled the php code so that you know how much time is taken up on the server to generate the page? it's likely, given the amount of html markup, that the transmission time from the server to the browser is where the bottleneck is at or in the browser rendering that much markup on the page.

Edited October 23, 2013 by mac_gyver

[joecooper]

d) have you profiled the php code so that you know how much time is taken up on the server to generate the page? it's likely, given the amount of html markup, that the transmission time from the server to the browser is where the bottleneck is at or in the browser rendering that much markup on the page.

 

Thank you for your reply. I have changed a lot of code to only add the entries that have boats, chests, boxs, have been clicked. Empty boxes don't have an entry on the DB.

 

I will work on AJAX to make it update only where needed, but thats a new thing for me, need to look in to it more, but was an ultimate goal.

 

Yes  I have a script to get load times, currently around a second to generate.

 

The problem I am having at the moment is the code that generates the grid isn't displaying correctly. It displays with the grid always empty (as in, clickable) and when you click on one that has already been clicked, it will give the error message. So the code that processes the clicks is working fine, just the displaying.

 

At the moment I have got a query to the database to get all the rows in the database. Then as it generates the grid, it checks if the current generating cell has a database entry. If it has an entry and that box has been clicked, it will display the clicked image. Although this isn't working.

<?php

//This file generates the grid for the users

if (!isset($including)) die("direct access not permitted");
$including=true;
include('config.php');

//Get jackpot info
$sql="SELECT * FROM jackpot WHERE `id` = '1'";
$result=mysql_query($sql);
$row = mysql_fetch_array( $result );
$prevamount = $row['prevamount'];
//

$query = "SELECT * FROM grid ORDER BY id ASC";
$result = mysql_query($query);
$row = mysql_fetch_array($result);


if (isset($_SESSION['loggedin'])){  //if the user has logged in - show the clickable grid

   if (isset($griderrormsg)){
       echo("<center><span style='background-color:white;color:red;font-size:12'>$griderrormsg</span></center>"); //display error
   }

   $cell = 0;
   echo("<TABLE BORDER=3 RULES=NONE FRAME=BOX width='20' border='0' cellspacing='0' cellpadding='0'");
   for($i=1; $i<=$gridlength; $i=$i+1){
                 echo "<tr>";
    for($j=1; $j<=$gridwidth; $j=$j+1){
		$CurrentCell = $i*$j;
		if($CurrentCell==$row['id'] && $row['clicked'] > 0){ //if the current cell exists in the database...
			$clicked = $row['clicked'];
			$hitby = $row['user'];
			$row = mysql_fetch_array($result); //Get next result from database
		
		}else{// current cell doesn't exist, leave as clickable
			$clicked = 0;
		
		}
		
		if ($clicked == "1"){   //if the cell was clicked
                echo("<td title='$j, $i'><img src='b.gif");
            }elseif ($clicked == "2"){   //if the ship was hit
                echo("<td title='$j, $i - Hit by $hitby'><img src='h.gif");
            }elseif ($clicked == "3"){  //if is chest
                echo("<td title='$j, $i - $prevamount btc won by $hitby'><img src='chest.gif");
            }elseif ($clicked == "4"){  //if is click box
                echo("<td title='$j, $i - Click Box Found By $hitby'><img src='box.png");
            }else{
                echo("<td><a href='index.php?c=" .($i*$j) . "'><img src='g.gif");
            }
            echo("' BORDER=0></td></a>");
		
		
			

            
       }

       echo("</tr>");
   }
   echo("</table>");
}else{  //if the user hasnt logged in, show read only grid
   
   //To do.
   
}
?>