The limits of PHP/MySQL

What is "too heavy"?

Page 1 of 1

4 Replies - 921 Views - Last Post: 23 May 2010 - 06:08 PM Rate Topic: -----

#1 the1corrupted   User is offline

  • D.I.C Head

Reputation: 13
  • View blog
  • Posts: 165
  • Joined: 31-March 09

The limits of PHP/MySQL

Posted 22 May 2010 - 12:18 PM

Hello all. I'm developing a game and had a quick question about the limitations of server processing and MySQL database processing.

Right now, I have a very database-intensive game, and I'm hoping to avoid lag, if any at all.

So far, the php script for the game-side alone has reached over a megabyte, and I'm trying to parse it out the best way I can.

My current method is using includes. If this, include that. If that, include this. Because there's a main game screen that has to stay relatively constant, I originally thought this was the best method.

But in each of these includes, I have a plethora of for, while, and other statements that process data.

For example, to see who all is with you, while (data=mysql_fetch_array($query)) is a common occurrence.

Is it best to query the database once (if applicable, as in to run a series of queries at the end) or try to use discretion with queries and updates? Or a method of both?

Should I make heavy use of MySQL indexing?

Is This A Good Question/Topic? 0
  • +

Replies To: The limits of PHP/MySQL

#2 Martyr2   User is offline

  • Programming Theoretician
  • member icon

Reputation: 5223
  • View blog
  • Posts: 14,002
  • Joined: 18-April 07

Re: The limits of PHP/MySQL

Posted 22 May 2010 - 12:39 PM

The idea you should be focusing on is how often you are making the query. If you can query the database and store the results for as long as possible, you are going to gain an advantage in that it will be quick for your PHP to access the results, it frees up the database to service others, and you even save on bandwidth from having to transfer the same data over and over again.

Another tip you should be looking at is when you pull your data from the database, are you pulling only the absolutely needed fields and records? You should not be doing select * very often if at all.

Indexing is only going to help if you have a large number of records and doing a lot of select statements. Keep in mind that indexing may even slow down any writes you are attempting to do to the database since it will take extra time to correctly insert the record into the index structure. So if you do a lot of database updating (as much as or more than you do selecting this could be a concern).

Last thing to watch out for is how often you are opening and closing connections to the database. Try not to do things like open and close a connection within a loop. I try to open one connection per page, do what is absolutely necessary for the page to function and then close it. All these tips go behind the idea of resource/IO management.

1) Pull only the data you must
2) Try to cache results if you are going to use the same data over and over again
3) Try to limit the number of queries you do make
4) Open, do your work and then get out.

These tips even work well with desktop software development which uses database backends. Hope this answers some of your questions. :)
Was This Post Helpful? 1
  • +
  • -

#3 CTphpnwb   User is online

  • D.I.C Lover
  • member icon

Reputation: 3795
  • View blog
  • Posts: 13,738
  • Joined: 08-August 08

Re: The limits of PHP/MySQL

Posted 22 May 2010 - 12:41 PM

Are you using classes? Have you separated output (most HTML) from php? Using functions and classes can dramatically reduce the size of your code, and splitting the view from the logic can make it easier to optimize your code as well.
Was This Post Helpful? 0
  • +
  • -

#4 the1corrupted   User is offline

  • D.I.C Head

Reputation: 13
  • View blog
  • Posts: 165
  • Joined: 31-March 09

Re: The limits of PHP/MySQL

Posted 23 May 2010 - 12:34 PM

I've actually started to move toward functions. Only to execute queries, and list results only when necessary. Instead of including the file like I've done in the past.

As for classes, I'm not too familiar with them. And I'm not too familiar with the "Module, View, Control" approach to php scripting either.

And I know of one particular instance where caching would be awesome, but I don't want to overload $_SESSION or $_COOKIE variables.

I have a bit that queries and fetches the array of a monster you fight in the game. It would be lovely if I could cache the array without too much overhead, or coding involved.

Here's the code bits:
<?php
include ("elmnt.inc.php");
$resource=mysql_query("SELECT * FROM `users`, `usr_stat` WHERE `users`.`id`=".$_SESSION['id']." AND `usr_stat`.`usr_id`=".$_SESSION['id'])
     or die ("<b>SQL Error:</b> ".mysql_error());

$player=mysql_fetch_array($resource);

$resource=mysql_query("SELECT * FROM `monsters` WHERE `id`=".$_GET['mob'])
     or die ("<b>SQL Error:</b> ".mysql_error());

$monster=mysql_fetch_assoc($resource);
$monster['level']=$_GET['lvl'];

$resource=mysql_query("SELECT `inventory`.`elmnt` FROM `usr_inv`, `inventory` WHERE 
	`usr_inv`.`usr_id`=".$player['id']." AND 
	`usr_inv`.`worn`=1 AND 
	`inventory`.`id`=`usr_inv`.`inv_id` AND
	`inventory`.`type`='shield'");

if (mysql_num_rows($resource)==1) {
	$player['elmnt']=mysql_fetch_assoc($resource);
	$player['elmnt']=$player['elmnt']['elmnt'];
} else {
	$player['elmnt']="none";
}

$resource=mysql_query("SELECT `stat0`,`mod0`,`stat1`,`mod1` FROM `usr_inv`, `inventory` WHERE
	`usr_inv`.`usr_id`=".$player['id']." AND
	`usr_inv`.`worn`=1 AND
	`inventory`.`id`=`usr_inv`.`inv_id`") or die ("SQL Error: ".mysql_error());

while ($data=mysql_fetch_assoc($resource)) {
	if (!empty($data['stat0']) AND !empty($data['mod0'])) {
		$player[$data['stat0']]+=$data['mod0'];
		$player[$data['stat0']]=number_format($player[$data['stat0']], 0);
		$base=10;
		if ($data['stat0']=="hp") {
			$base=30;
		}
		if ($player[$data['stat0']]>$base+5) {
			$player[$data['stat0']]=$base+5;
		}
	}
	if (!empty($data['stat1']) AND !empty($data['mod1'])) {
		$player[$data['stat1']]+=$data['mod1'];
		$player[$data['stat1']]=number_format($player[$data['stat1']], 0);
		$base=15;
		if ($data['stat1']=="hp") {
			$base=30;
		}
		if ($player[$data['stat1']]>$base+5) {
			$player[$data['stat1']]=$base+5;
		}
	}
}

This post has been edited by the1corrupted: 23 May 2010 - 12:38 PM

Was This Post Helpful? 0
  • +
  • -

#5 aklo   User is offline

  • D.I.C Head

Reputation: 18
  • View blog
  • Posts: 233
  • Joined: 23-January 09

Re: The limits of PHP/MySQL

Posted 23 May 2010 - 06:08 PM

I'm not too sure about game program but looking at your code above you definitely want to use class. Learn php classes if you are not familar with it...the class can reduce your code to something like:


http://www.dreaminco...op/page__st__30

Look for "aklo" (me) and see the little paging class i did. Look at how long the paging class is...but when i actually use it in my page, i only need 2-3 lines.

I think i'll just copy the codes here.

class paging { 
                 
        public $perpage; 
        public $total; 
        public $totalpage;       
        public $id; 
        public $data; 
 
        //Total count of array 
        public function getTotal(){ 
                return $this->total; 
        } 
 
        public function setTotal($a){ 
                $this->total = count($a); 
        } 
         
        //Define items to display per page  
        public function setPerPage($a){ 
                $this->perpage = $a; 
                return $this->perpage; 
        } 
 
        //Total page 
        public function getTotalPage(){ 
                $this->getTotal(); 
                $this->totalpage = ceil($this->total / $this->perpage); 
                return $this->totalpage; 
        } 
 
        //Check/set page id 
        public function setID(){ 
                if (!(isset($_GET['id'])) || $_GET['id']<=0) { 
                        $this->id = 0; 
                } elseif ($_GET['id']>=$this->totalpage) { 
                        $this->id=$this->totalpage-1; 
                } else { 
                        $this->id = $_GET['id']; 
                } 
                return $this->id; 
        } 
 
        //Set which array to display 
        public function display($array, $result_per_page=1){ 
                $this->setTotal($array); 
                $this->setPerPage($result_per_page); 
                $this->getTotalPage(); 
                $this->setID(); 
 
                $jump = $this->id * $this->perpage; 
 
                $this->data = array_slice($array, $jump, $this->perpage); 
                foreach ($this->data as $key =>$value) { 
                        echo $this->data[$key] . "<br />"; 
                } 
 
 
                if ($this->id<=0){ 
                        $this->id = 0; 
                        $next = $this->id +1; 
                        $output .= "<a href=\"{$_SERVER['PHP_SELF']}?id={$next}\">" . "next" . "</a>"; 
                } 
 
                if ($this->id>0 && $this->id<($this->totalpage -1)) { 
         
                        $previous = $this->id -1 ; 
                        $output .= "<a href=\"{$_SERVER['PHP_SELF']}?id={$previous}\">" . "Prev" . "</a>" . " | "; 
 
 
                        $next = $this->id +1; 
                        $output .= "<a href=\"{$_SERVER['PHP_SELF']}?id={$next}\">" . "next" . "</a>"; 
                } 
 
                if ($this->id >= ($this->totalpage - 1)) { 
                        $this->id = $this->totalpage - 1; 
                        $previous = $this->id -1 ; 
                        $output .= "<a href=\"{$_SERVER['PHP_SELF']}?id={$previous}\">" . "Prev" . "</a>"; 
                } 
 
                echo $output; 
        } 
}


I only need to write the entire chunk above once and when i actually use it..


$a = array("a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z"); 
 
//Instantiation 
$paging = new paging(); 
 
//Param1 = array to display 
//Param2 = Number of results perpage 
echo $paging->display($a,4);


I only need these few lines.

This post has been edited by aklo: 23 May 2010 - 06:09 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1