PHP, lots of queries, slow page loads... ?

Hello,


On a site i use heavy graphics, but also lots of queries on some pages.

The graphics loading time is acceptable, but before a page actually starts loading sometimes takes a few seconds. (I click a link, status bar in browser says: 'waiting for...')
The site is on virtual hosting and i'm not sure if the virtual hosting is causing this slowless or the amount of queries.
Is there any way i can test this out and maybe tips on how to improving this speed or optimizing php and mysql queries ?

 

Take the queries and execute them in PHPMyAdmin. It will tell yuo how many seconds they take. Anything over 1 second is normally a crap query you need to optimize.

After you ran it, there's an 'Explain' button. You'll have to read some tutorials on the net about MySQL query optimization with the help of explain. Once yuo get the hang of that (took me an hour and a half - no rocket science) you will know where to add an index, alter a table structure and which query to change.

 

Hi, i tried what you said in phpmyadmin.
A total of 35 queries took 0.0049 seconds.
So i guess that can't be the problem ?

Here is the site btw:

edit

What else could cause the slow loadtimes ?

 

Loads alright for me.

Stick your URL in NetMechanic.com's free tool: Result

The result will probably disappear in a while. Here's the URL of the tool: http://netmechanic.com/products/HTML_Toolbox_FreeSample.shtml

As you can see in the load time report you just have too many large images.

Not many people scroll anyway so I'd get rid of most of the stuff below the fold.

 

Thanks TOPS30, I didn't know about NetMechanic . It has some really cool tools for webmaster. Very Helpful.

 

all depends on queries types, database structure and number of tables.
What script do yo use?

 

I don't know what your queries are doing but 35 for one page sounds excessive? Is there anyway you can do fewer queries which collect more data? I've found that if you do this it works quicker ... you can parse the resultant array queries quicker than running the query again, if you see what I mean?

Can you tell us what it is you're extracting from the database(s)?

 

35 queries could work faster then 1 query - depends on the query. mysql has caching feature, and if queries are simple, then results could be taken from cache

 

It's just a page where i pull lots of data from different tables. But these are mostly simple SELECTS and COUNT queries. I don't think it can be done in less queries. But i tested them and the queries itself are pretty quick.
So i guess it's the virtual hosting (hosted with 18 other sites on same server) that causes slow performance ?

 

What do you mean "what script" ?
Do you only work with pre-programmed scripts ?

 

OK guys I'm going to suggest the obvious and say why not use a wrapper function for the mysql_query() call so that you can easily capture debug information?

Let me give an example...

First, create a basic debug mode function like this:

	/**
	 * Function to set and return debug mode
	 * @author Bobby Easland 
	 * @version 1.0
	 * @return boolean   
	 */	
	function debugMode(){
		switch(true){
			case ( isset($_GET['output']) ):
				if ( (int)$_GET['output'] == 1 ){
					$_SESSION['output'] = (int)$_GET['output'];
					return true;
				} else {
					if ( isset($_SESSION['output']) ){
						unset($_SESSION['output']);
					}
					return false;
				}
				break;
			case ( isset($_SESSION['output']) && $_SESSION['output'] == 1 ):
				return true;
				break;
			default:
				return false;
				break;
		}
		return;
	}

PHP:

OK...so now that you have the debugMode() function you can easily slip into and out of debug mode like this:

• Activate -> xxxyy.com/script.php?output=1
• Deactivate -> xxxyy.com/script.php?output=0

The next step is to create your basic wrapper function for mysql_query(). I know that you probably encapsulate your functions just for situations like this but I'll give a short example anyway:

	/*
	 * Wrapper function for database query
	 * @author Bobby Easland 
	 * @version 1.0
	 * @param string $sql Standard SQL statement
	 * @param resource $linkID
	 * @return mixed Returns resource on success or boolean false on failure
	 */
	function dbQuery($sql, $linkID){
		if ( false !== ($resource = mysql_query($sql, $linkID)) ){
			return $resource;
		}
		return false;
	}

PHP:

You will need to refactor the wrapper function to allow capture of diagnostic data. Something like this will work:

	/*
	 * Wrapper function for database query
	 * @author Bobby Easland 
	 * @version 1.1
	 * @param string $sql Standard SQL statement
	 * @param resource $linkID
	 * @return mixed Returns resource on success or boolean false on failure
	 */
	function dbQuery($sql, $linkID){
		if ( debugMode() === true ){
			global $debug;
			static $increment = 0;
			$start = explode(' ', microtime());
		}
		
		if ( false !== ($resource = mysql_query($sql, $linkID)) ){
			return $resource;
		}
		
		if ( debugMode() === true ){
			$time = number_format( array_sum( explode(' ', microtime()) ) - array_sum($start), 6, '.', ',');
			$debug[$increment]['QUERY'] = $sql;
			$debug[$increment]['TIME'] = $time;
			if ( is_resource($resource) ){
				$debug[$increment]['NUM_ROWS'] = mysql_num_rows($resource);
			}
			$increment++;
		}

		return false;
	}

PHP:

With this code in place when in debug mode every query will capture basic diagnostic data. To view the captured data you simply have to do something like this near the bottom of your script:

<pre>
	<?php print_r($debug); ?>
</pre>

PHP:

The data captured is very basic and the only meaningful metric is the time it took to execute the SQL. So, you could also add a little more advanced code and capture the EXPLAIN data provided by MySQL. Refactor the wrapper to look something like this and add another function:

	/*
	 * Wrapper function for database query
	 * @author Bobby Easland 
	 * @version 1.2
	 * @param string $sql Standard SQL statement
	 * @param resource $linkID
	 * @return mixed Returns resource on success or boolean false on failure
	 */
	function dbQuery($sql, $linkID){
		if ( debugMode() === true ){
			global $debug;
			static $increment = 0;
			$start = explode(' ', microtime());
		}
		
		if ( false !== ($resource = mysql_query($sql, $linkID)) ){
			return $resource;
		}
		
		if ( debugMode() === true ){
			$time = number_format( array_sum( explode(' ', microtime()) ) - array_sum($start), 6, '.', ',');
			$debug[$increment]['QUERY'] = $sql;
			$debug[$increment]['TIME'] = $time;
			if ( is_resource($resource) ){
				$debug[$increment]['NUM_ROWS'] = mysql_num_rows($resource);
			}
			if ( false !== eregi("select", $sql) ){
				$debug[$increment]['EXPLAIN'] = dbExplainQuery($sql, $linkID, $increment);
			}
			$increment++;
		}

		return false;
	}
	
	/*
	 * Function to retrieve MySQL Explain data
	 * @author Bobby Easland 
	 * @version 1.1
	 * @param string $sql Standard SQL statement
	 * @param resource $linkID
	 * @param integer $increment
	 * @return array Explain data for query
	 */
	function dbExplainQuery($sql, $linkID){
		$explain = 'EXPLAIN ' . $sql;
		$query = mysql_query($sql, $linkID);
		$data = array();
		if ( is_resource($query) && mysql_num_rows($query) > 0 ){
			while($result = mysql_fetch_array($query)){
				$data[] = $result;
			}
		}
		return $data;
	}

PHP:

With all of this implemented you should now have the tools available to fully diagnose each query on any page that is refactored to use the wrapper function.

Enjoy!

Bobby

 

great info chemo
Thanks!

 

Thank you...if you need any help getting it implemented or analyzing the data just post back.

Bobby