Too many queries and search load time is mega slow

I’ve got a website that I’ve taken over the management of and when running a search for products it is excruciatingly slow! It’s not so bad when there are quite a few search options selected but for a blank search it takes around 12-15 seconds to load the search results (and then does so when you click next page, previous page which doesn’t seem right).

I’m no expert at all, I can normally just about work things out by myself when managing the websites (there’s another site which is 10 times as fast with a similar number of products). The only difference I can see between the two is that on the slow site there is a stock table set us as the one “product” can have various sizes and different prices associated to these prices.

Here’s some of the code that runs in the search, I’d greatly appreciate it if somebody could have a look and if anything sticks out as wrong give me a nudge in the right direction. Thanks…

<?php
if ($conditions['id'] == "")
  unset($conditions['id']);

// handle id/keyword searching
if (is_numeric($conditions['keywords']))
{
  $conditions['id'] = intval($conditions['keywords']);
  unset($conditions['keywords']);
}


if ($_GET['debug'] == 1)
  print_r($conditions);


//		$sql  = "SELECT main_picture, rugs.id as id, length, width, shape,
//                    rugs.type as type, rugs.subtype, fabric, pattern,
//                    colour1, colour2, colour3, price, discount_price,
//                    rugs.stock, rugs.active, rugs.views, rugs.sold,
//                    rug_types.name as type_name,
//                    rug_types.description as type_description,
//                    rug_fabrics.name as fabric_name,
//                    rug_patterns.name as pattern_name,
//                    rug_subtypes.name as subtype_name,
//                    rug_shape.name as shape,
//                    rug_designer.name as rug_designer,
//                    rug_designnames.name as design_name,
//                    colours1.name as colour1_name,
//                    colours2.name as colour2_name,
//                    colours3.name as colour3_name,
//                    IF(price IS NULL OR discount_price IS NULL,
//                       COALESCE(price, discount_price), LEAST(price, discount_price)) as actual_price
//             FROM rugs, rug_types, rug_fabrics, rug_patterns, rug_subtypes,
//                  rug_shape, rug_designer, rug_designnames,
//                  rug_colours as colours1, rug_colours as colours2, rug_colours as colours3 ";
//		$sql .= "WHERE rugs.type        = rug_types.id
//               AND rugs.subtype     = rug_subtypes.id
//               AND rugs.fabric      = rug_fabrics.id
//               AND rugs.pattern     = rug_patterns.id
//               AND rugs.shape       = rug_shape.id
//               AND rugs.designer    = rug_designer.id
//               AND rugs.design_name = rug_designnames.id
//               AND rugs.colour1     = colours1.id
//               AND rugs.colour2     = colours2.id
//               AND rugs.colour3     = colours3.id
//               AND ";

//		$sql  = "SELECT main_picture, rugs.id as id, shape,
//                    rugs.type as type, rugs.subtype, fabric, pattern,
//                    colour1, colour2, colour3,
//                    rugs.active, rugs.views,
//                    rug_types.name as type_name,
//                    rug_types.description as type_description,
//                    rug_fabrics.name as fabric_name,
//                    rug_patterns.name as pattern_name,
//                    rug_subtypes.name as subtype_name,
//                    rug_shape.name as shape,
//                    rug_designer.name as rug_designer,
//                    rug_designnames.name as design_name,
//                    colours1.name as colour1_name,
//                    colours2.name as colour2_name,
//                    colours3.name as colour3_name
//             FROM rugs, rug_types, rug_fabrics, rug_patterns, rug_subtypes,
//                  rug_shape, rug_designer, rug_designnames,
//                  rug_colours as colours1, rug_colours as colours2, rug_colours as colours3 ";
$agg = 'min';
if ($sort=='price_desc') $agg = 'max';
if ($sort=='price_asc')  $agg = 'min';

$base_sql = "SELECT DISTINCT rugs.id as id,
              (SELECT $agg((IF(price IS NULL OR discount_price IS NULL,
        COALESCE(price, discount_price), LEAST(price, discount_price)))) FROM rugs_stock WHERE rugs_stock.rug_id = rugs.id) as actual_price
             FROM rugs, rug_types, rug_fabrics, rug_patterns, rug_subtypes,
                  rug_shape, rug_designer, rug_designnames,
                  rug_colours as colours1, rug_colours as colours2, rug_colours as colours3, rugs_stock as stock ";
//changed "as count" to "as actual_price" to solve sort by price problem - may have problems elsewhere if so change back
$count_sql = "SELECT COUNT(DISTINCT rugs.id,
              (SELECT $agg((IF(price IS NULL OR discount_price IS NULL,
        COALESCE(price, discount_price), LEAST(price, discount_price)))) FROM rugs_stock WHERE rugs_stock.rug_id = rugs.id)) as actual_price
             FROM rugs, rug_types, rug_fabrics, rug_patterns, rug_subtypes,
                  rug_shape, rug_designer, rug_designnames,
                  rug_colours as colours1, rug_colours as colours2, rug_colours as colours3, rugs_stock as stock ";

$sql .= "WHERE ";
if ( !isset($_GET['admin-search']) && empty($_GET['admin-search']) )
  $sql .= " rugs.active = '1' AND ";
$sql .= "          rugs.type        = rug_types.id
               AND rugs.subtype     = rug_subtypes.id
               AND rugs.fabric      = rug_fabrics.id
               AND rugs.pattern     = rug_patterns.id
               AND rugs.shape       = rug_shape.id
               AND rugs.designer    = rug_designer.id
               AND rugs.design_name = rug_designnames.id
               AND rugs.colour1     = colours1.id
               AND rugs.colour2     = colours2.id
               AND rugs.colour3     = colours3.id
               AND rugs.id          = stock.rug_id
                ";
if (strlen($conditions['keywords']) > 0)
{
  // do keyword stuff
  $keywords = split(" ", $conditions['keywords']);

  // clean any ignore words
  foreach ($keywords as $key => $keyword)
  {
    if (in_array(strtolower($keyword), $ignore_list))
      unset($keywords[$key]);
  }

  $key_tables = array('rug_designer', 'rug_designnames', 'rug_fabrics', 'rug_patterns', 'rug_shape',
    'rug_types', 'rug_subtypes', 'colours1', 'colours2', 'colours3');
  $sql_array = array();

  foreach ($keywords as $keyword)
  {
    $keyword_array = array();
    foreach ($key_tables as $table)
    {
      //$keyword_array[] = $table . ".name LIKE LOWER('" . strtolower($keyword) . "%') ";
      $keyword_array[] = $table . ".name REGEXP '[[:<:]]".$keyword."[[:>:]]' ";


    }
    // do the same for mod_trad field
    $keyword_array[] = "rugs.mod_trad LIKE LOWER('". strtolower($keyword) ."%') ";


    // implode the keyword likes with 'OR'
    $sql_array[] = "( " . implode(' OR ', $keyword_array) . ") ";

  }

  $sql .= " AND ( ";
  // implode the phrases with 'AND'
  $sql .= implode(' AND ', $sql_array);
  $sql .= ") ";

}
else
{
  if (intval($conditions['type']) > 0)
  {
    $sql .= " AND rugs.type = '" . intval($conditions['type']) . "' ";
  }
  if (intval($conditions['subtype']) > 0)
  {
    $sql .= " AND rugs.subtype = '" . intval($conditions['subtype']) . "' ";
  }
  if (intval($conditions['design_name']) > 0)
  {
    $sql .= " AND rugs.design_name = '" . intval($conditions['design_name']) . "' ";
  }
  if (intval($conditions['designer']) > 0)
  {
    $sql .= " AND rugs.designer = '" . intval($conditions['designer']) . "' ";
  }
  if (intval($conditions['pattern']) > 0)
  {
    $sql .= " AND rugs.pattern = '" . intval($conditions['pattern']) . "' ";
  }
  if (intval($conditions['fabric']) > 0)
  {
    $sql .= " AND rugs.fabric = '" . intval($conditions['fabric']) . "' ";
  }
  if (intval($conditions['shape']) > 0)
  {
    $sql .= " AND rugs.shape = '" . intval($conditions['shape']) . "' ";
  }
  if(is_numeric($conditions['kids']))
  {
    $sql .= " AND rugs.kids = '".$conditions['kids']."' ";
  }
  if ( $conditions['mod_trad'] != 'Any' )
  {
    if ($conditions['mod_trad'] == 'Modern')
    {
      $sql .= " AND ( rugs.mod_trad = 'Modern' OR rugs.mod_trad = 'Either' ) ";
    }
    else if ($conditions['mod_trad'] == 'Traditional')
    {
      $sql .= " AND ( rugs.mod_trad = 'Traditional' OR rugs.mod_trad = 'Either' ) ";
    }
    else if ($conditions['mod_trad'] == 'Either')
    {
      $sql .= " AND rugs.mod_trad = 'Either' ";
    }
  }

	if(intval($conditions['colour'])>0) {
		$colour = intval($conditions['colour']);
		$sql .= " AND ( rugs.colour1 = '$colour' or rugs.colour2 = '$colour' or rugs.colour3 = '$colour' ) ";
	}
  else
  {
    if (count($conditions['colours']) > 0)
    {
      foreach ($conditions['colours'] as $colour)
      {
        $sql .= " AND ( rugs.colour1 = '" . $colour . "' OR
                        rugs.colour2 = '" . $colour . "' OR
                        rugs.colour3 = '" . $colour . "' ) ";
      }
    }
  }

  if (strlen($conditions['price_from']) > 0)
  {
    $conditions['price'] = $conditions['price_from'];
    if (isset($conditions['price_to']))
      $conditions['price'] = $conditions['price_from'] . "-" . $conditions['price_to'];
  }

  if (isset($conditions['price']) && $conditions['price'] != "")
  {
    $conditions['price'] = split("-", $conditions['price']);
    if (count($conditions['price']) == 2)
    {
      $pairs[] = "IF(stock.price IS NULL OR stock.discount_price IS NULL,
                    COALESCE(stock.price, stock.discount_price),
                  LEAST(stock.price, stock.discount_price))>='". floatval($conditions['price'][0]) . "'";
      $pairs[] = "IF(stock.price IS NULL OR stock.discount_price IS NULL,
                    COALESCE(stock.price, stock.discount_price),
                  LEAST(stock.price, stock.discount_price))<='". floatval($conditions['price'][1]) . "'";
    }
    else {
      $pairs[] = "IF(stock.price IS NULL OR stock.discount_price IS NULL,
                    COALESCE(stock.price, stock.discount_price),
                  LEAST(stock.price, stock.discount_price))>='". floatval($conditions['price'][0]) . "'";
    }
  }


//  if ($conditions['action2'] == 'search')
//  {
//    if ($conditions['size'] == 'm')
//    {
      if ($conditions['length_m_from'] == "" and $conditions['length_m_to'] == "")
      {
        $conditions['length_m_from'] = 0;
        $conditions['length_m_to'] = 9;
      }
      if ($conditions['width_m_from'] == "" and $conditions['width_m_to'] == "")
      {
        $conditions['width_m_from'] = 0;
        $conditions['width_m_to'] = 9;
      }

      $conditions['length'] = $conditions['length_m_from'] . "-" . $conditions['length_m_to'];
      $conditions['width'] = $conditions['width_m_from'] . "-" . $conditions['width_m_to'];
//    }
//    else
//    {
//      if ($conditions['length_ft_from'] == "" and $conditions['length_ft_to'] == "")
//      {
//        $conditions['length_ft_from'] = 0;
//        $conditions['length_ft_to'] = 9;
//      }
//      if ($conditions['width_ft_from'] == "" and $conditions['width_ft_to'] == "")
//      {
//        $conditions['width_ft_from'] = 0;
//        $conditions['width_ft_to'] = 9;
//      }
//      // imperial/metric conversion
//      $m_conv = 0.3048;
//      $i_conv = 0.0254;
//
//      $imperial_length_from = $m_conv * intval($conditions['length_ft_from']);
//      $imperial_length_from_inches = $i_conv * intval($conditions['inch_length_from']);
//      $length_from = $imperial_length_from + $imperial_length_from_inches;
//      $imperial_length_to = $m_conv * intval($conditions['length_ft_to']);
//      $imperial_length_to_inches = $i_conv * intval($conditions['inch_length_to']);
//      $length_to = $imperial_length_to + $imperial_length_to_inches;
//      $conditions['length'] = $length_from . "-" . $length_to;
//
//      $imperial_width_from = $m_conv * intval($conditions['width_ft_from']);
//      $imperial_width_from_inches = $i_conv * intval($conditions['inch_width_from']);
//      $width_from = $imperial_width_from + $imperial_width_from_inches;
//      $imperial_width_to = $m_conv * intval($conditions['width_ft_to']);
//      $imperial_width_to_inches = $i_conv * intval($conditions['inch_width_to']);
//      $width_to = $imperial_width_to + $imperial_width_to_inches;
//      $conditions['width'] = $width_from . "-" . $width_to;
//    }
//  }

	if (!isset($conditions['length']) or !isset($conditions['width'])) {
		$pairs[] = "stock.length>='0'";
		$pairs[] = "stock.width>='0'";
		$pairs[] = 'stock.stock > 0';
	}

  if (isset($conditions['length']) && $conditions['length'] != "")
  {
    $conditions['length'] = split("-", $conditions['length']);
    if (count($conditions['length']) == 2)
    {
      $pairs[] = "stock.length>='" . floatval($conditions['length'][0]) . "'";
      $pairs[] = "stock.length<='" . floatval($conditions['length'][1]) . "'";
    }
    else
      $pairs[] = "stock.length>='" . floatval($conditions['length'][0]) . "'";
  }

  if (isset($conditions['width']) && $conditions['width'] != "")
  {
    $conditions['width'] = split("-", $conditions['width']);
    if (count($conditions['width']) == 2)
    {
      $pairs[] = "stock.width>='" . floatval($conditions['width'][0]) . "'";
      $pairs[] = "stock.width<='" . floatval($conditions['width'][1]) . "'";
    }
    else
      $pairs[] = "stock.width>='" . floatval($conditions['width'][0]) . "'";
  }


  if (isset($conditions['id']) && !isset($conditions['admin-search']))
  {
    $sql .= " AND rugs.id = '" . intval($conditions['id']) . "' ";
  }

  if ( count($pairs)>0 )
    $sql .= " AND ".join(" AND ", $pairs);

} // end of keyword skip

// search via admin area will show zero stock and inactive
//    if ( isset($_GET['admin-search']) && !empty($_GET['admin-search']) && $_GET['admin-search'] == 1 )
//  		$sql .= " AND rugs.deleted=0 ";
//    else
//		  $sql .= " AND rugs.active=1 AND rugs.deleted=0 AND (rugs.stock > 0 OR rugs.stock_override = 1) ";
//
if(!is_numeric($_GET['rand_seed'])) $_GET['rand_seed'] = intval(rand());
switch($sort){
	case "price_asc":
		$sort = " ORDER BY actual_price ASC, RAND(".intval($_GET['rand_seed']).")";
		break;
	case "price_desc":
		$sort = " ORDER BY actual_price DESC, RAND(".intval($_GET['rand_seed']).")";
		break;
	case "created":
		$sort = " ORDER BY rugs.id DESC, RAND(".intval($_GET['rand_seed']).")";
		break;
	case "oldest":
		$sort = " ORDER by rugs.id ASC, RAND(".intval($_GET['rand_seed']).")";
		break;
	case "special_offer":
		$sort = " ORDER by discount_price DESC, price DESC, RAND(".intval($_GET['rand_seed']).")";
		break;
	default:
		if($conditions['mod_trad']){
			$sort = " ORDER BY FIELD(rugs.mod_trad, 'Modern', 'Traditional', 'Either'), RAND(".intval($_GET['rand_seed']).")";
		}else{
			$sort = " ORDER BY RAND(".intval($_GET['rand_seed']).")";
		}
		break;
}

$sql .= $sort;

// do base and count sql statements
$count_sql .= $sql;
$sql = $base_sql.$sql;
//echo $sql;

if ($_GET['debug'] == 1)
  echo print_r($conditions);

Good luck debugging that… it looks like something written by somebody who had no idea what he was doing. Is it possible to scrap and start again from the beginning? I’d pretty much refuse to work on a script this bad…

It’s a pretty well established website so I don’t know how practical it would be to start again…

Here’s the code from our other website which seems to load in a second or two. Is there much difference?

&lt;?php
	if($conditions['id'] == "")
		unset($conditions['id']);

	if($_GET['debug']==1)
		print_r($conditions);

		if($conditions['action2'] == 'search') {
			if($conditions['size'] == 'm') {
				if($conditions['length_m_from'] == "" and $conditions['length_m_to'] == "") {
					$conditions['length_m_from'] = 0;
					$conditions['length_m_to'] = 9;
				}
				if($conditions['width_m_from'] == "" and $conditions['width_m_to'] == "") {
					$conditions['width_m_from'] = 0;
					$conditions['width_m_to'] = 9;
				}

				$conditions['length'] = $conditions['length_m_from']."-".$conditions['length_m_to'];
				$conditions['width'] = $conditions['width_m_from']."-".$conditions['width_m_to'];
			} else {
				if($conditions['length_ft_from'] == "" and $conditions['length_ft_to'] == "") {
					$conditions['length_ft_from'] = 0;
					$conditions['length_ft_to'] = 9;
				}
				if($conditions['width_ft_from'] == "" and $conditions['width_ft_to'] == "") {
					$conditions['width_ft_from'] = 0;
					$conditions['width_ft_to'] = 9;
				}
				// imperial/metric conversion
				$m_conv = 0.3048;
				$i_conv = 0.0254;

				$imperial_length_from = $m_conv * intval($conditions['length_ft_from']);
				$imperial_length_from_inches = $i_conv * intval($conditions['inch_length_from']);
				$length_from = $imperial_length_from + $imperial_length_from_inches;
				$imperial_length_to = $m_conv * intval($conditions['length_ft_to']);
				$imperial_length_to_inches = $i_conv * intval($conditions['inch_length_to']);
				$length_to = $imperial_length_to + $imperial_length_to_inches;
				$conditions['length'] = $length_from ."-". $length_to;

				$imperial_width_from = $m_conv * intval($conditions['width_ft_from']);
				$imperial_width_from_inches = $i_conv * intval($conditions['inch_width_from']);
				$width_from = $imperial_width_from + $imperial_width_from_inches;
				$imperial_width_to = $m_conv * intval($conditions['width_ft_to']);
				$imperial_width_to_inches = $i_conv * intval($conditions['inch_width_to']);
				$width_to = $imperial_width_to + $imperial_width_to_inches;
				$conditions['width'] = $width_from ."-". $width_to;
			}
			if(strlen($conditions['price_from'])&gt;0) {
				$conditions['price'] = $conditions['price_from'];
				if(isset($conditions['price_to']))
					$conditions['price'] = $conditions['price_from']."-".$conditions['price_to'];
			}
		}

		if (!isset($conditions['length']) or !isset($conditions['width'])) {
			$pairs[] = "`length`&gt;='0'";
			$pairs[] = "`width`&gt;='0'";
		}

		if (isset($conditions['length']) && $conditions['length'] != "") {
			$conditions['length'] = split("-", $conditions['length']);
			if (count($conditions['length']) == 2) {
				$pairs[] = "`length`&gt;='".floatval($conditions['length'][0])."'";
				$pairs[] = "`length`&lt;='".floatval($conditions['length'][1])."'";
			}
			else
				$pairs[] = "`length`&gt;='".floatval($conditions['length'][0])."'";
		}

		if (isset($conditions['width']) && $conditions['width'] != "") {
			$conditions['width'] = split("-", $conditions['width']);
			if (count($conditions['width']) == 2) {
				$pairs[] = "`width`&gt;='".floatval($conditions['width'][0])."'";
				$pairs[] = "`width`&lt;='".floatval($conditions['width'][1])."'";
			}
			else
				$pairs[] = "`width`&gt;='".floatval($conditions['width'][0])."'";
		}

		if(isset($conditions['price']) && $conditions['price'] != "") {
			$conditions['price'] = split("-", $conditions['price']);
			if(count($conditions['price']) == 2) {
				$pairs[] = "`price`&gt;='".floatval($conditions['price'][0])."'";
				$pairs[] = "`price`&lt;='".floatval($conditions['price'][1])."'";
			}
			else
				$pairs[] = "`price`&gt;='".floatval($conditions['price'][0])."'";
		}


		$sql = "SELECT main_picture, rugs.id as id,length,width, shape, rugs.type as type, rugs.subtype, fabric, pattern, colour1, colour2, colour3, price, discount_price, rug_types.name as type_name, rug_types.description as type_description, rug_fabrics.name as fabric_name, rug_patterns.name as pattern_name, rug_subtypes.name as subtype_name, rug_shape.name as shape, rug_designer.name as rug_designer, rug_designnames.name as design_name FROM rugs,rug_types,rug_fabrics,rug_patterns,rug_subtypes,rug_shape,rug_designer,rug_designnames ";
		
		$sql .= " WHERE rugs.type=rug_types.id AND rugs.subtype=rug_subtypes.id AND rugs.fabric = rug_fabrics.id AND rugs.pattern = rug_patterns.id AND rugs.shape = rug_shape.id AND rugs.designer = rug_designer.id AND rugs.design_name = rug_designnames.id AND ";
		if(strlen($conditions['keyword'])) { 
			$sql .= " AND rugs_patterns.name = 'Luxury' ";
			echo $sql;
			// do keyword stuff
		} else {

		if(intval($conditions['type'])&gt;0) {
			$sql .= " rugs.type = '".intval($conditions['type'])."' AND  ";
		}
		if(intval($conditions['subtype'])&gt;0) {
			$sql .= " rugs.subtype = '".intval($conditions['subtype'])."' AND  ";
		}
		if(intval($conditions['pattern'])&gt;0) {
			$sql .= " rugs.pattern = '".intval($conditions['pattern'])."' AND ";
		}
		if(intval($conditions['fabric'])&gt;0) {
			$sql .= " rugs.fabric = '".intval($conditions['fabric'])."' AND ";
		}
		if($conditions['mod_trad'] == 'Modern') {
			$sql .= " rugs.mod_trad = 'Modern' AND ";
		} elseif($conditions['mod_trad'] == 'Traditional') {
			$sql .= " rugs.mod_trad = 'Traditional' AND ";
		}

		if(intval($conditions['colour'])&gt;0) {
			$colour = intval($conditions['colour']);
			$sql .= " ( rugs.colour1 = '$colour' or rugs.colour2 = '$colour' or rugs.colour3 = '$colour' ) AND ";
		}
		
		if(isset($conditions['id'])) {
			$sql .= " rugs.id = '".intval($conditions['id'])."' AND ";
		}

		$sql .= join(" AND ", $pairs);

		} // end of keyword skip

		$sql .= " AND rugs.active=1 AND rugs.deleted=0 AND (rugs.stock &gt; 0 OR rugs.stock_override = 1) ";

		if ($sort != "") {
			switch ($sort) {
				case "price_asc":
					$sort = " ORDER BY `price` ASC";
					break;
				case "price_desc":
					$sort = " ORDER BY `price` DESC";
					break;
				case "created":
					$sort = " ORDER BY rugs.id DESC";
					break;
				case "oldest":
					$sort = " ORDER by rugs.id ASC";
					break;
				case "special_offer":
					$sort = " ORDER by discount_price,rugs.id";
					break;
                default:
                    $sort = " ORDER BY `price` ASC";
			}	
		}
        else
            $sort = " ORDER BY `price` ASC";
        $sql .= $sort;
        //echo $sql;

	if($_GET['debug'] ==1)
		echo print_r($conditions);

Not being funny, but this code is truly awful and was clearly written by an amateur. I can’t even read it, sorry. Perhaps other people here will go through the pain barrier for you.

If I inherited code this bad, I’d be calling for a rewrite of the whole thing. I reckon the code is that bad.

In terms of giving you some constructive advice: I’d recommend looking into a practice more formally known as refactoring, which is where you take an existing code base and restructure it to make it more readable and more efficient, but with the same overall outcome (in this case you’d be trying to make the code more readable and trying to speed up the database query at the end of it).

Sorry if my posts so far seem flippant, but I’ve come across code like this before and I know how horrible it is to deal with. It’s just been hacked together by someone who doesn’t know how to write a maintainable system. This system will be almost impossible to maintain in the long term (imagine trying to change how the search engine works now, or add new categories and stuff look that - virtually impossible I’m afraid. You could break the existing system easily and you may not even know it’s broken).

Another tool you should look into is called ‘xdebug’. Xdebug is an excellent tool that will allow you to set breakpoints in your code, and then you can step through the code one line at a time and see what is happening with all the variables each step of the way. I would recommend learning how to setup and use xdebug, as this will surely be the only realistic way you’re going to stand any real chance at all.

As an aside - for the love of god, make sure you put the code in version control (something like GIT or Mercurial), because then you’ll be able to see your changes over time and will be able to revert back to the original version if something breaks.

A couple of refactoring books I’d recommend:

Xdebug:
http://xdebug.org/

Version control:

Git: http://git-scm.com/
Mercurial: http://mercurial.selenic.com/wiki/

A good tutorial for Mercurial:

http://hginit.com

Other than that I’m afraid I personally can’t help you much more, other than to tell you that the code is so bad I just don’t want to go through the torture of trying to unravel it (xdebug will help you immensely with that).

One thing that sticks out to me is that when $conditions[‘keywords’] exists that filters are applied using regexp.


  foreach ($keywords as $keyword) 
  { 
    $keyword_array = array(); 
    foreach ($key_tables as $table) 
    { 
      //$keyword_array[] = $table . ".name LIKE LOWER('" . strtolower($keyword) . "%') "; 
      $keyword_array[] = $table . ".name REGEXP '[[:<:]]".$keyword."[[:>:]]' "; 
       
       
    } 
    // do the same for mod_trad field 
    $keyword_array[] = "rugs.mod_trad LIKE LOWER('". strtolower($keyword) ."%') "; 

     
    // implode the keyword likes with 'OR' 
    $sql_array[] = "( " . implode(' OR ', $keyword_array) . ") "; 

  } 

However, that is not being done in the other code you posted after. Regular expressions in MySQL are very costly. One should *try to avoid them if possible.

As mentioned though the entire thing is really quit the mess. The other thing I would check is indexes but it looks like the second set of code joins the same tables. So if that one runs decently than that probably isn’t the issue. Though it would be good to check the table indexes for the foreign keys.

All those ‘if’ statements in the first sql query can’t be helping, either. Also, the RAND() function called near the end will mean you’re never taking advantage of Mysql’s internal cache engine, as the query will be ran and executed each time, instead of taking advantage of the cache.

Also, this:


  AND rugs.colour1     = colours1.id                                    
               AND rugs.colour2     = colours2.id                                    
               AND rugs.colour3     = colours3.id

Smacks of really bad database design. Colour2??

You’ve got a subquery with an if statement in it in the first query, and you’re using native mysql functions within that, which means no query cache for you. It’s all wrapped up in some horrific spaghetti php code that is virtually impenetrable, so really, you’ve got your work cut out for you.

One starting place if you really can’t change this and actually make it readable would be to start by echoing out the resulting query at the very end of the script. If you do that and then paste the query into mysql, you can start playing about with it to see if you can fix it that way…

Hopefully we can at least help the OP troubleshoot.

Mal, start by inserting timers – calls to microtime(true) – to ensure you know exactly where the bottleneck is. If you’ve narrowed it down to one particular SQL query, then print out the full and final query – not the PHP that generates it – for you and for us to inspect and toy with. Once you’ve isolated the slow-running query and can toy with it and manipulate it in a dev environment, start removing parts, one piece at a time, to discover which part of the query is causing the slowdown. My money’s on the subquery. You’ll also want to inspect and compare the indexes between the slow- and fast-running sites.

But before you do any of this: make sure you’re using version control!

Setup version control first, then start doing this, so if you do break anything, you can at least revert back to a working (I guess that term can be relative eh) version at any point.

Yes, I agree. Version control is one of the basics that you should always, always, always use.

Agreed. @Mal1, if you’re not familiar with version control, please check out http://hginit.com - it’s a Mercurial tutorial. Mercurial and GIT are the two common version control systems used today, and they both work in very similar ways. Going through that tutorial should teach you enough to start using both. I would take a few hours of time to get to know how this works, and THEN attack your problem (the security of being able to flip back to previous versions at any point in time will be worth all the effort you put into learning if anything goes wrong).

After that you can start debugging and trying to figure out how to improve the script.

It’s probably a minor point, but I would debate this claim. By far, the two most common version control systems used today are Git and SVN. Even the defunct CVS is probably used more than Mercurial. Which isn’t to say that Mercurial isn’t good, but just that we should be realistic about it’s usage.

Fair enough if that’s the case. We use Mercurial every day in work, so I guess I’m just used to it. I’d recommend the distributed model over SVN any day though (I really dislike svn) - so go with either GIT or Mercurial. GIT is no doubt the more commonly used DVCS. I’m just used to Mercurial because we use it every day in work.

Thank you all for the replies. Don’t know if I’ll be able to fix the problem on my own but it definitely gives me somewhere to start, if I can even work out what is causing it to be slow then that’ll be half the battle (if it’s one specific thing causing most of the ‘damage’).

Thanks!

Still trying to work out microtime for individual queries… we already had it running from the index.php displaying on every page which shows number of queries as 1 or 2 on static pages with millisecond execution times, around 12 queries to view a specific product (execution time of 1-2 seconds), and around 120-140 queries and an execution time of 8-15+ seconds for search pages.

I’ve taken out sections of code at a time and ran searches from the code I posted above and there was no noticeable change which to me would suggest it’s the code in general or else coming from one of the other class files:

Database.class

&lt;?php
class Database {
	var $pages, $error, $query_count;

	function Database($db_user, $db_pass, $db_name, $db_host) {
		$this-&gt;query_count = 0;
		$this-&gt;error = 0;

		if (!mysql_connect($db_host, $db_user, $db_pass) || !mysql_select_db($db_name))
			$this-&gt;error = 1;
	}

	function query($sql_query) {
		//echo $sql_query.'&lt;br /&gt;';
		$this-&gt;query_count++;
		$result = mysql_query($sql_query);

		$rows = array();
		if(@mysql_num_rows($result) &gt; 0){
			while ($row = mysql_fetch_assoc($result))
				$rows[] = $row;
		}
		return $rows;
	}

	function escape($escape_string) {
		if (get_magic_quotes_gpc())
			return mysql_escape_string($escape_string);
		else
			return $escape_string;
	}

	function execute($sql_query) {
		mysql_query($sql_query) or die("EXECUTE: ".mysql_error()." SQL: ".$sql_query);
	//	echo "TSDXX: $sql_query";
	//	die();

		return mysql_insert_id();
	}

	function save($table, $fields) {
		$pairs = array();

		foreach ($fields as $field =&gt; $value)
      // do not quote NULLs
      if ( $value == "NULL" )
        $pairs[] = "`$field`=".$this-&gt;escape($value);
      else
    		$pairs[] = "`$field`='".$this-&gt;escape($value)."'";

		$sql = "UPDATE `$table` SET ".join(",", $pairs)." WHERE `id`='{$fields['id']}'";

//    echo $sql;
//    die();
//		print_r($fields);
//		die();
		$this-&gt;execute($sql);
	}

	function randomRugs($page=1) {
		global $search_results_per_page;
echo "Test";
		if (!isset($_SESSION['random_rugs']) || !is_array($_SESSION['random_rugs']))
			$_SESSION['random_rugs'] = $this-&gt;query("SELECT `id` FROM `rugs` WHERE `active`='1' AND `deleted`='0' ORDER BY RAND()");

		$this-&gt;pages = ceil(count($_SESSION['random_rugs'])/$search_results_per_page);

		$page = intval($page);
		if ($page &gt; $this-&gt;pages)
			$page = $this-&gt;pages;
		if ($page &lt; 1)
			$page = 1;

		$current_ids = array_slice($_SESSION['random_rugs'], ($page-1)*$search_results_per_page, $search_results_per_page);

		$ids = array();
		foreach ($current_ids as $current_id)
			$ids[] = "`id`='".$current_id['id']."'";

		//return $this-&gt;query("SELECT * FROM `rugs` WHERE ".join(" OR ", $ids));
		$rugs=array();
		foreach($ids as $id) {
			$rugs=array_merge($rugs, $this-&gt;query("SELECT * FROM `rugs` WHERE ".$id.";"));

		}
		return $rugs;
	}










function findRugs($conditions, $page=1, $sort="") {
	global $search_results_per_page, $current_user, $pages;

	if ($current_user != NULL){
		$search_fields = array("id", "active", "type", "mod_trad", "shape", "rug_type", "fabric", "design", "make", "age", "origin", "name", "pattern", "colour");
	}else{
		$search_fields = array("id", "active", "type", "mod_trad",  "shape", "rug_type", "fabric", "design", "make", "age", "origin", "name", "pattern", "colour");
	}
	$pairs = array();
	$ignore_rs = $this-&gt;query("SELECT LOWER(name) as name FROM rug_search_ignore_words WHERE id &lt;&gt; 0 AND active = 1");
	$ignore_list = array();
	foreach ( $ignore_rs as $ignore ){
		$ignore_list[] = $ignore['name'];
	}

	require("inc_search.php");
	// collect entire result set for refine area
	//$rugs_all = $this-&gt;query($sql);

	//$rugs_search_all = $this-&gt;query($sql);
	$sql_all = $sql;

	$rugs_all = array();
	foreach ( $rugs_search_all as $rug ) {
		$tmp = new Rug($rug['id']);
		$rugs_all[] = $tmp-&gt;data;
	}

	//echo "rug_count: ".count($rugs_all)."&lt;br /&gt;";
	//echo "&lt;br /&gt;---FULL---".$sql;
	$page_count = $this-&gt;query($count_sql);
	$pages = ceil(array_shift(array_shift($page_count)) / $search_results_per_page);
	$search_results_per_page = 10;
	if(isset($_GET['page'])) {
		$cur_page = intval($_GET['page'])-1;
		if($cur_page==0) {
			$sql .= " LIMIT 0, $search_results_per_page";
		} else {			
			$sql .= " LIMIT ".($search_results_per_page * $cur_page).", $search_results_per_page";
		}
	} else {
		$sql .= " LIMIT 0, $search_results_per_page";
	}
	//echo $sql;
	// page the result set			
	$rugs_search = $this-&gt;query($sql);
	$rugs = array();
	foreach ( $rugs_search as $rug ) {
		$tmp = new Rug($rug['id']);
		
		// apply promotions
		$tmp-&gt;applyPromotion();
		if(!isset($_GET['admin-search']) OR (isset($_GET['admin-search']) && $_GET['admin-search'] == 0)){
		
			// eliminate rugs without any stock
			if ( $tmp-&gt;useQuantity() ) {
				$stock = 0;
				// look for active stock
				foreach ( $tmp-&gt;data['stock']-&gt;data as $stock =&gt; $values ) {
					if ( $values['active'] == '1' && $values['stock'] &gt; 0 ){
						$stock += $values['stock'];
					}
				}
				if ( $stock &gt; 0 ){
					$rugs[] = $tmp-&gt;data;
				}
			}else{
				$rugs[] = $tmp-&gt;data;
			}
		}else{
			$rugs[] = $tmp-&gt;data;
		}

	}
	return array( 'this' =&gt; $rugs, 'all' =&gt; $rugs_all, 'sql' =&gt; $sql_all) ;
}
















	function findNextPrev($conditions, $cnt, $sort="") {
		global $search_results_per_page, $current_user;

		unset($conditions['id']);

		if ($current_user != NULL)
			$search_fields = array("id", "active", "type", "shape", "rug_type", "fabric", "design", "make", "age", "origin", "name");
		else
			$search_fields = array("id", "active", "type", "shape", "rug_type", "fabric", "design", "make", "age", "origin", "name");

		require("inc_search.php");
		
		$rugs = $this-&gt;query($sql);

		return $rugs;
	}


	function findOrders($conditions) {
		global $current_user;

		if ($current_user != NULL)
			$search_fields = array("id", "active", "shape", "rug_type", "fabric", "design", "make", "age", "origin", "name");
		else
			$search_fields = array("id", "active", "shape", "rug_type", "fabric", "design", "make", "age", "origin", "name");

		$pairs = array();

		foreach ($conditions as $field =&gt; $value)
			if (in_array($field, $search_fields) && trim($value) != "")
				if ($field == "created")
					$pairs[] = "DATE(`$field`)='".$this-&gt;escape($value)."'";
				else
					$pairs[] = "`$field`='".$this-&gt;escape($value)."'";

		if (isset($conditions['length']) && $conditions['length'] != "") {
			$conditions['length'] = split("-", $conditions['length']);
			if (count($conditions['length']) == 2) {
				$pairs[] = "`length`&gt;='".floatval($conditions['length'][0])."'";
				$pairs[] = "`length`&lt;='".floatval($conditions['length'][1])."'";
			}
			else
				$pairs[] = "`length`&gt;='".floatval($conditions['length'][0])."'";
		}

		if (isset($conditions['width'])) {
			$conditions['width'] = split("-", $conditions['width']);
			if (count($conditions['width']) == 2) {
				$pairs[] = "`width`&gt;='".floatval($conditions['width'][0])."'";
				$pairs[] = "`width`&lt;='".floatval($conditions['width'][1])."'";
			}
			else
				$pairs[] = "`width`&gt;='".floatval($conditions['width'][0])."'";
		}

		if (isset($conditions['price'])) {
			$conditions['price'] = split("-", $conditions['price']);
			if (count($conditions['price']) == 2) {
				$pairs[] = "`price`&gt;='".intval($conditions['price'][0])."'";
				$pairs[] = "`price`&lt;='".intval($conditions['price'][1])."'";
			}
			else
				$pairs[] = "`price`&gt;='".intval($conditions['price'][0])."'";
		}

		if (isset($conditions['colours']))
			$pairs[] = "`colours` LIKE '%,".join(",", $conditions['colours']).",%'";

		if (isset($conditions['time_period'])) {
			switch($conditions['time_period']) {
				case "date":
					$pairs[] = "DATE(`orders`.`created`) >= '".$this->escape($conditions['date_from'])."'";
					$pairs[] = "DATE(`orders`.`created`) <= '".$this->escape($conditions['date_to'])."'";
					break;
				case "last_day":
					$pairs[] = "`orders`.`created`>=NOW() - INTERVAL 1 DAY";
					break;
				case "last_week":
					$pairs[] = "`orders`.`created`>=NOW() - INTERVAL 7 DAY";
					break;
				case "last_month":
					$pairs[] = "`orders`.`created`>=NOW() - INTERVAL 1 MONTH";
					break;
			}
		}
		$pairs[] = "`orders`.`state`='accepted'";

		$orders = $this->query("SELECT * FROM `orders_rugs` LEFT JOIN (`rugs`,`orders`) ON (`orders_rugs`.`rug_id`=`rugs`.`id` AND `orders`.`id`=`orders_rugs`.`order_id`) ".(count($pairs)>0?"WHERE ".join(" AND ", $pairs):"")." ORDER BY `orders_rugs`.`order_id`");

		return $orders;
	}

	function findRugsByKeywords($keywords) {
		$keywords = split(" ", $keywords);

		$pairs = array();
		foreach ($keywords as $keyword)
			$pairs[] = "UPPER(`keyword`)=UPPER('".$this->escape($keyword)."')";

		$rugs = $this->query("SELECT `rug_id`, COUNT(*) as `cnt` FROM `keywords` WHERE ".join(" OR ", $pairs)." GROUP BY `rug_id`;");
		$ids = array();
		foreach ($rugs as $rug)
		    if($rug['cnt']>=count($pairs))  {
			$cnt=0;
			foreach ($keywords as $keyword) {
			    $res=mysql_query("SELECT COUNT(*) FROM `keywords` WHERE UPPER(`keyword`)=UPPER('".$this->escape($keyword)."') AND `rug_id`='".$rug['rug_id']."';");
			    if(mysql_result($res,0)>0)
				$cnt++;
			}
			if($cnt>=count($pairs))
			    $ids[] = $rug['rug_id'];
		    }

		return $ids;
	}

	function listRugs($page=1,$sort='') {
		global $rugs_listing_per_page;

		$rugs = $this->query("SELECT COUNT(*) as `how_much` FROM `rugs` WHERE `deleted`='0'");
		$this->pages = ceil($rugs[0]['how_much'] / $rugs_listing_per_page);

		$page = intval($page);
		if ($page > $this->pages)
			$page = $this->pages;
		if ($page < 1)
			$page = 1;

		$offset = ($page-1)*$rugs_listing_per_page;

		switch ($sort) {
			case "views_asc":
				$sort = "views";
				$direction = "ASC";
				break;
			case "views_desc":
				$sort = "views";
				$direction = "DESC";
			        break;
			case "activeASC":
				$sort = "active";
				$direction = "ASC";
				break;

			case "createdASC":
				$sort = "created";
				$direction = "ASC";
				break;
			case "special_offer":
                $sort = "discount_price";
                $direction = "DESC";
                break;
			case "price_desc":
                $sort = "actual_price";
                $direction = "DESC";
                break;
			case "price_asc":
                $sort = "actual_price";
                $direction = "ASC";
                break;
			case "rug_id_desc":
                $sort = "id";
                $direction = "DESC";
                break;
			case "rug_id_asc":
                $sort = "id";
                $direction = "ASC";
                break;			
			case "stock_asc":
				$sort = "stock";
				$direction = "ASC";
				break;			
			case "stock_desc":
				$sort = "stock";
				$direction = "DESC";
				break;

			case "active":
			case "created":

			case "sold":
				$direction = "DESC";
				break;

			default:
				$sort = "id";
				$direction = "DESC";
				break;
		}

        $rugs = $this->query("SELECT * FROM `rugs` WHERE `deleted`='0' ORDER BY `$sort` $direction LIMIT $offset, $rugs_listing_per_page");

        // pickup the stock
        foreach ( $rugs as $key => $rug ) {
          $stock = new Stock($rug['id'],0);
          $rugs[$key]['stock'] = $stock;
        }

        return $rugs;
	}

	function listUsers($page=1) {
		global $users_listing_per_page;

		$users = $this->query("SELECT COUNT(*) as `how_much` FROM `users`");
		$this->pages = ceil($users[0]['how_much'] / $users_listing_per_page);

		$page = intval($page);
		if ($page > $this->pages)
		$page = $this->pages;
		if ($page < 1)
		$page = 1;

		$offset = ($page-1)*$users_listing_per_page;

		$users = $this->query("SELECT * FROM `users` LIMIT $offset, $users_listing_per_page");

		return $users;
	}

	function listOrders($page=1, $sort='', $rug_id = NULL ) {
		global $orders_listing_per_page;

    $query = "SELECT *, ord.id as order_id FROM orders ord, order_states st
                            WHERE ord.active = 1 AND ord.order_state = st.id ";
    if ( !is_null($rug_id) )
      $query .= " AND ord.id IN ( SELECT order_id FROM orders_rugs
                                   WHERE stock_id IN ( SELECT id FROM rugs_stock WHERE rug_id = '$rug_id' ) ) ";

		$orders = $this->query($query);
		$this->pages = ceil($orders[0]['how_much'] / $orders_listing_per_page);

		$page = intval($page);
		if ($page > $this->pages)
			$page = $this->pages;
		if ($page < 1)
			$page = 1;

		$offset = ($page-1)*$orders_listing_per_page;

		switch ($sort) {
          case "name":
            $sort = "lastname, firstname";
            $direction = "ASC";
            break;
          case "created_asc":
            $sort = "created";
            $direction = "ASC";
            break;
          case "created_desc":
            $sort = "created";
            $direction = "DESC";
            break;
          case "total_asc":
            $sort = "total";
            $direction = "ASC";
            break;
          case "total_desc":
            $sort = "total";
            $direction = "DESC";
            break;
          case "state":
            $sort = "order_state";
            $direction = "DESC";
            break;
          case "manual":
            $sort = "manual";
            $direction = "DESC";
            break;
          default:
            $sort = "ord.id";
            $direction = "DESC";
            break;
		}

    $query .= " ORDER BY $sort $direction LIMIT $offset, $orders_listing_per_page ";

		$orders = $this->query($query);

		return $orders;
	}

	function listPromotions($page=1,$sort='') {
		global $rugs_listing_per_page;

		$promos = $this->query("SELECT COUNT(*) as `how_much` FROM `promotions ");
		$this->pages = ceil($promos[0]['how_much'] / $rugs_listing_per_page);

		$page = intval($page);
		if ($page > $this->pages)
			$page = $this->pages;
		if ($page < 1)
			$page = 1;

		$offset = ($page-1)*$rugs_listing_per_page;

		switch ($sort) {
			default:
				$sort = "id";
				$direction = "DESC";
				break;
		}

    $promos = $this->query("SELECT * FROM `promotions` ORDER BY `$sort` $direction LIMIT $offset, $rugs_listing_per_page");

    return $promos;
	}

	function listVouchers($page=1,$sort='') {
		global $rugs_listing_per_page;

		$vouchers = $this->query("SELECT COUNT(*) as `how_much` FROM `vouchers ");
		$this->pages = ceil($promos[0]['how_much'] / $rugs_listing_per_page);

		$page = intval($page);
		if ($page > $this->pages)
			$page = $this->pages;
		if ($page < 1)
			$page = 1;

		$offset = ($page-1)*$rugs_listing_per_page;

		switch ($sort) {
			default:
				$sort = "id";
				$direction = "DESC";
				break;
		}

    $vouchers = $this->query("SELECT * FROM `vouchers` ORDER BY `$sort` $direction LIMIT $offset, $rugs_listing_per_page");

    return $vouchers;
	}

	function getStats() {
		$stats = array();
    $total_stock_query      = "SELECT sum(stock) AS output FROM rugs_stock
                               WHERE active = '1'
                                 AND rug_id IN
                                (SELECT id FROM rugs WHERE active='1' and deleted ='0') ;";
    $total_cost_query       = "SELECT sum(cost_price * stock) AS output FROM rugs_stock
                               WHERE active = '1'
                                 AND rug_id IN
                                (SELECT id FROM rugs WHERE active='1' and deleted ='0') ;";
    $total_price_query      = "SELECT sum(price * stock) AS output FROM rugs_stock
                               WHERE active = '1'
                                 AND rug_id IN
                                (SELECT id FROM rugs WHERE active='1' and deleted ='0') ;";
    $total_sale_stock_query = "SELECT sum(stock) AS output FROM rugs_stock
                               WHERE (   discount_price IS NOT NULL
                                      OR discount_price_override IS NOT NULL )
                                 AND active = '1'
                                 AND rug_id IN
                                (SELECT id FROM rugs WHERE active='1' and deleted ='0') ;";
		$total_sale_cost_query = "SELECT sum(cost_price * stock) AS output FROM rugs_stock
                               WHERE (   discount_price IS NOT NULL
                                      OR discount_price_override IS NOT NULL )
                                 AND active = '1'
                                 AND rug_id IN
                                (SELECT id FROM rugs WHERE active='1' and deleted ='0') ;";
    $total_sale_price_query = "SELECT sum( IF(discount_price IS NULL OR discount_price_override IS NULL,
                                           COALESCE(discount_price_override, discount_price),
                                  LEAST(discount_price, discount_price_override)) * stock) AS output FROM rugs_stock
                               WHERE (   discount_price IS NOT NULL
                                      OR discount_price_override IS NOT NULL )
                                 AND active = '1'
                                 AND rug_id IN
                                (SELECT id FROM rugs WHERE active='1' and deleted ='0') ;";

    $total_stock_result      = $this-&gt;query($total_stock_query);
    $total_cost_result       = $this-&gt;query($total_cost_query);
    $total_price_result      = $this-&gt;query($total_price_query);
    $total_sale_stock_result = $this-&gt;query($total_sale_stock_query);
    $total_sale_cost_result  = $this-&gt;query($total_sale_cost_query);	
    $total_sale_price_result = $this-&gt;query($total_sale_price_query);

    $stats['total_stock'] = $total_stock_result[0]['output'];
    $stats['total_cost']  = '&pound; '. number_format($total_cost_result[0]['output'], 2);
    $stats['total_price'] = '&pound; '. number_format($total_price_result[0]['output'], 2);
    $stats['total_sale_stock'] = $total_sale_stock_result[0]['output'];
	  $stats['total_sale_cost']  = '&pound; '. number_format($total_sale_cost_result[0]['output'], 2);
    $stats['total_sale_price'] = '&pound; '. number_format($total_sale_price_result[0]['output'], 2);

		return $stats;
	}

  function findVoucher($code, $rugs) {

    $rug_list = array();
    foreach ($rugs as $rug)
      $rug_list[] = $rug['id'];

    $rug_criteria = '('.implode(',', $rug_list).')';
    echo $rug_criteria;

    // check the validity of a voucher code
    $sql = "SELECT * FROM vouchers WHERE voucher_code = '$code' ";
    $result = $this-&gt;query($sql);

    if ( count($result) &gt; 0 ) {
      // now check for rug validity
      $rug_sql = stripslashes($result[0]['criteria']);
      echo $rug_sql;

      $rug_result = $this-&gt;query($rug_sql);

      $rugs = array();
      foreach ( $rug_result as $rug )
        $rugs[] = $rug['id'];

      $rug_found = false;
      foreach ( $rug_list as $rug )
      {
        if ( in_array($rug, $rugs) ) {
          $rug_found = true;
        }
      }

      if ( $rug_found )
        return($result[0]['id']);
      else
        return 0;

    }
    else
      return 0;


  }
}
?&gt;

Then there’s stock.class… :-/

Narrowed the main culprit (time-wise) down to this small section of code within the database class:


	//echo $sql;
	// page the result set			
	$rugs_search = $this->query($sql);
	$rugs = array();
	foreach ( $rugs_search as $rug ) {
		$tmp = new Rug($rug['id']);

From the database:


&lt;?php

class Database {
	var $pages, $error, $query_count;

	function Database($db_user, $db_pass, $db_name, $db_host) {
		$this-&gt;query_count = 0;
		$this-&gt;error = 0;

		if (!mysql_connect($db_host, $db_user, $db_pass) || !mysql_select_db($db_name))
			$this-&gt;error = 1;
	}

	function query($sql_query) {
		//echo $sql_query.'&lt;br /&gt;';
		$this-&gt;query_count++;
		$result = mysql_query($sql_query);

		$rows = array();
		if(@mysql_num_rows($result) &gt; 0){
			while ($row = mysql_fetch_assoc($result))
				$rows[] = $row;
		}
		return $rows;
	}

	function escape($escape_string) {
		if (get_magic_quotes_gpc())
			return mysql_escape_string($escape_string);
		else
			return $escape_string;
	}

	function execute($sql_query) {
		mysql_query($sql_query) or die("EXECUTE: ".mysql_error()." SQL: ".$sql_query);
	//	echo "TSDXX: $sql_query";
	//	die();

		return mysql_insert_id();
	}

	function save($table, $fields) {
		$pairs = array();

		foreach ($fields as $field =&gt; $value)
      // do not quote NULLs
      if ( $value == "NULL" )
        $pairs[] = "`$field`=".$this-&gt;escape($value);
      else
    		$pairs[] = "`$field`='".$this-&gt;escape($value)."'";

		$sql = "UPDATE `$table` SET ".join(",", $pairs)." WHERE `id`='{$fields['id']}'";

//    echo $sql;
//    die();
//		print_r($fields);
//		die();
		$this-&gt;execute($sql);
	}

	function randomRugs($page=1) {
		global $search_results_per_page;
echo "Test";
		if (!isset($_SESSION['random_rugs']) || !is_array($_SESSION['random_rugs']))
			$_SESSION['random_rugs'] = $this-&gt;query("SELECT `id` FROM `rugs` WHERE `active`='1' AND `deleted`='0' ORDER BY RAND()");

		$this-&gt;pages = ceil(count($_SESSION['random_rugs'])/$search_results_per_page);

		$page = intval($page);
		if ($page &gt; $this-&gt;pages)
			$page = $this-&gt;pages;
		if ($page &lt; 1)
			$page = 1;

		$current_ids = array_slice($_SESSION['random_rugs'], ($page-1)*$search_results_per_page, $search_results_per_page);

		$ids = array();
		foreach ($current_ids as $current_id)
			$ids[] = "`id`='".$current_id['id']."'";

		//return $this-&gt;query("SELECT * FROM `rugs` WHERE ".join(" OR ", $ids));
		$rugs=array();
		foreach($ids as $id) {
			$rugs=array_merge($rugs, $this-&gt;query("SELECT * FROM `rugs` WHERE ".$id.";"));

		}
		return $rugs;
	}










function findRugs($conditions, $page=1, $sort="") {
	global $search_results_per_page, $current_user, $pages;

	if ($current_user != NULL){
		$search_fields = array("id", "active", "type", "mod_trad", "shape", "rug_type", "fabric", "design", "make", "age", "origin", "name", "pattern", "colour");
	}else{
		$search_fields = array("id", "active", "type", "mod_trad",  "shape", "rug_type", "fabric", "design", "make", "age", "origin", "name", "pattern", "colour");
	}
	$pairs = array();
	$ignore_rs = $this-&gt;query("SELECT LOWER(name) as name FROM rug_search_ignore_words WHERE id &lt;&gt; 0 AND active = 1");
	$ignore_list = array();
	foreach ( $ignore_rs as $ignore ){
		$ignore_list[] = $ignore['name'];
	}

	require("inc_search.php");
	// collect entire result set for refine area
	//$rugs_all = $this-&gt;query($sql);

	//$rugs_search_all = $this-&gt;query($sql);
	$sql_all = $sql;

	$rugs_all = array();
	foreach ( $rugs_search_all as $rug ) {
		$tmp = new Rug($rug['id']);
		$rugs_all[] = $tmp-&gt;data;
	}

	//echo "rug_count: ".count($rugs_all)."&lt;br /&gt;";
	//echo "&lt;br /&gt;---FULL---".$sql;
	$page_count = $this-&gt;query($count_sql);
	$pages = ceil(array_shift(array_shift($page_count)) / $search_results_per_page);
	$search_results_per_page = 10;
	if(isset($_GET['page'])) {
		$cur_page = intval($_GET['page'])-1;
		if($cur_page==0) {
			$sql .= " LIMIT 0, $search_results_per_page";
		} else {			
			$sql .= " LIMIT ".($search_results_per_page * $cur_page).", $search_results_per_page";
		}
	} else {
		$sql .= " LIMIT 0, $search_results_per_page";
	}
	$_SESSION['start_time'] = microtime(true);
	//echo $sql;
	// page the result set			
	$rugs_search = $this-&gt;query($sql);
	$rugs = array();
	foreach ( $rugs_search as $rug ) {
		$tmp = new Rug($rug['id']);
		$_SESSION['end_time'] = microtime(true);
		
		// apply promotions
		$tmp-&gt;applyPromotion();
		if(!isset($_GET['admin-search']) OR (isset($_GET['admin-search']) && $_GET['admin-search'] == 0)){
		
			// eliminate rugs without any stock
			if ( $tmp-&gt;useQuantity() ) {
				$stock = 0;
				// look for active stock
				foreach ( $tmp-&gt;data['stock']-&gt;data as $stock =&gt; $values ) {
					if ( $values['active'] == '1' && $values['stock'] &gt; 0 ){
						$stock += $values['stock'];
					}
				}
				if ( $stock &gt; 0 ){
					$rugs[] = $tmp-&gt;data;
				}
			}else{
				$rugs[] = $tmp-&gt;data;
			}
		}else{
			$rugs[] = $tmp-&gt;data;
		}

	}
	return array( 'this' =&gt; $rugs, 'all' =&gt; $rugs_all, 'sql' =&gt; $sql_all) ;
}
















	function findNextPrev($conditions, $cnt, $sort="") {
		global $search_results_per_page, $current_user;

		unset($conditions['id']);

		if ($current_user != NULL)
			$search_fields = array("id", "active", "type", "shape", "rug_type", "fabric", "design", "make", "age", "origin", "name");
		else
			$search_fields = array("id", "active", "type", "shape", "rug_type", "fabric", "design", "make", "age", "origin", "name");

		require("inc_search.php");
		
		$rugs = $this-&gt;query($sql);

		return $rugs;
	}


	function findOrders($conditions) {
		global $current_user;

		if ($current_user != NULL)
			$search_fields = array("id", "active", "shape", "rug_type", "fabric", "design", "make", "age", "origin", "name");
		else
			$search_fields = array("id", "active", "shape", "rug_type", "fabric", "design", "make", "age", "origin", "name");

		$pairs = array();

		foreach ($conditions as $field =&gt; $value)
			if (in_array($field, $search_fields) && trim($value) != "")
				if ($field == "created")
					$pairs[] = "DATE(`$field`)='".$this-&gt;escape($value)."'";
				else
					$pairs[] = "`$field`='".$this-&gt;escape($value)."'";

		if (isset($conditions['length']) && $conditions['length'] != "") {
			$conditions['length'] = split("-", $conditions['length']);
			if (count($conditions['length']) == 2) {
				$pairs[] = "`length`&gt;='".floatval($conditions['length'][0])."'";
				$pairs[] = "`length`&lt;='".floatval($conditions['length'][1])."'";
			}
			else
				$pairs[] = "`length`&gt;='".floatval($conditions['length'][0])."'";
		}

		if (isset($conditions['width'])) {
			$conditions['width'] = split("-", $conditions['width']);
			if (count($conditions['width']) == 2) {
				$pairs[] = "`width`&gt;='".floatval($conditions['width'][0])."'";
				$pairs[] = "`width`&lt;='".floatval($conditions['width'][1])."'";
			}
			else
				$pairs[] = "`width`&gt;='".floatval($conditions['width'][0])."'";
		}

		if (isset($conditions['price'])) {
			$conditions['price'] = split("-", $conditions['price']);
			if (count($conditions['price']) == 2) {
				$pairs[] = "`price`&gt;='".intval($conditions['price'][0])."'";
				$pairs[] = "`price`&lt;='".intval($conditions['price'][1])."'";
			}
			else
				$pairs[] = "`price`&gt;='".intval($conditions['price'][0])."'";
		}

		if (isset($conditions['colours']))
			$pairs[] = "`colours` LIKE '%,".join(",", $conditions['colours']).",%'";

		if (isset($conditions['time_period'])) {
			switch($conditions['time_period']) {
				case "date":
					$pairs[] = "DATE(`orders`.`created`) >= '".$this->escape($conditions['date_from'])."'";
					$pairs[] = "DATE(`orders`.`created`) <= '".$this->escape($conditions['date_to'])."'";
					break;
				case "last_day":
					$pairs[] = "`orders`.`created`>=NOW() - INTERVAL 1 DAY";
					break;
				case "last_week":
					$pairs[] = "`orders`.`created`>=NOW() - INTERVAL 7 DAY";
					break;
				case "last_month":
					$pairs[] = "`orders`.`created`>=NOW() - INTERVAL 1 MONTH";
					break;
			}
		}
		$pairs[] = "`orders`.`state`='accepted'";

		$orders = $this->query("SELECT * FROM `orders_rugs` LEFT JOIN (`rugs`,`orders`) ON (`orders_rugs`.`rug_id`=`rugs`.`id` AND `orders`.`id`=`orders_rugs`.`order_id`) ".(count($pairs)>0?"WHERE ".join(" AND ", $pairs):"")." ORDER BY `orders_rugs`.`order_id`");

		return $orders;
	}

	function findRugsByKeywords($keywords) {
		$keywords = split(" ", $keywords);

		$pairs = array();
		foreach ($keywords as $keyword)
			$pairs[] = "UPPER(`keyword`)=UPPER('".$this->escape($keyword)."')";

		$rugs = $this->query("SELECT `rug_id`, COUNT(*) as `cnt` FROM `keywords` WHERE ".join(" OR ", $pairs)." GROUP BY `rug_id`;");
		$ids = array();
		foreach ($rugs as $rug)
		    if($rug['cnt']>=count($pairs))  {
			$cnt=0;
			foreach ($keywords as $keyword) {
			    $res=mysql_query("SELECT COUNT(*) FROM `keywords` WHERE UPPER(`keyword`)=UPPER('".$this->escape($keyword)."') AND `rug_id`='".$rug['rug_id']."';");
			    if(mysql_result($res,0)>0)
				$cnt++;
			}
			if($cnt>=count($pairs))
			    $ids[] = $rug['rug_id'];
		    }

		return $ids;
	}

	function listRugs($page=1,$sort='') {
		global $rugs_listing_per_page;

		$rugs = $this->query("SELECT COUNT(*) as `how_much` FROM `rugs` WHERE `deleted`='0'");
		$this->pages = ceil($rugs[0]['how_much'] / $rugs_listing_per_page);

		$page = intval($page);
		if ($page > $this->pages)
			$page = $this->pages;
		if ($page < 1)
			$page = 1;

		$offset = ($page-1)*$rugs_listing_per_page;

		switch ($sort) {
			case "views_asc":
				$sort = "views";
				$direction = "ASC";
				break;
			case "views_desc":
				$sort = "views";
				$direction = "DESC";
			        break;
			case "activeASC":
				$sort = "active";
				$direction = "ASC";
				break;

			case "createdASC":
				$sort = "created";
				$direction = "ASC";
				break;
			case "special_offer":
                $sort = "discount_price";
                $direction = "DESC";
                break;
			case "price_desc":
                $sort = "actual_price";
                $direction = "DESC";
                break;
			case "price_asc":
                $sort = "actual_price";
                $direction = "ASC";
                break;
			case "rug_id_desc":
                $sort = "id";
                $direction = "DESC";
                break;
			case "rug_id_asc":
                $sort = "id";
                $direction = "ASC";
                break;			
			case "stock_asc":
				$sort = "stock";
				$direction = "ASC";
				break;			
			case "stock_desc":
				$sort = "stock";
				$direction = "DESC";
				break;

			case "active":
			case "created":

			case "sold":
				$direction = "DESC";
				break;

			default:
				$sort = "id";
				$direction = "DESC";
				break;
		}

        $rugs = $this->query("SELECT * FROM `rugs` WHERE `deleted`='0' ORDER BY `$sort` $direction LIMIT $offset, $rugs_listing_per_page");

        // pickup the stock
        foreach ( $rugs as $key => $rug ) {
          $stock = new Stock($rug['id'],0);
          $rugs[$key]['stock'] = $stock;
        }

        return $rugs;
	}

	function listUsers($page=1) {
		global $users_listing_per_page;

		$users = $this->query("SELECT COUNT(*) as `how_much` FROM `users`");
		$this->pages = ceil($users[0]['how_much'] / $users_listing_per_page);

		$page = intval($page);
		if ($page > $this->pages)
		$page = $this->pages;
		if ($page < 1)
		$page = 1;

		$offset = ($page-1)*$users_listing_per_page;

		$users = $this->query("SELECT * FROM `users` LIMIT $offset, $users_listing_per_page");

		return $users;
	}

	function listOrders($page=1, $sort='', $rug_id = NULL ) {
		global $orders_listing_per_page;

    $query = "SELECT *, ord.id as order_id FROM orders ord, order_states st
                            WHERE ord.active = 1 AND ord.order_state = st.id ";
    if ( !is_null($rug_id) )
      $query .= " AND ord.id IN ( SELECT order_id FROM orders_rugs
                                   WHERE stock_id IN ( SELECT id FROM rugs_stock WHERE rug_id = '$rug_id' ) ) ";

		$orders = $this->query($query);
		$this->pages = ceil($orders[0]['how_much'] / $orders_listing_per_page);

		$page = intval($page);
		if ($page > $this->pages)
			$page = $this->pages;
		if ($page < 1)
			$page = 1;

		$offset = ($page-1)*$orders_listing_per_page;

		switch ($sort) {
          case "name":
            $sort = "lastname, firstname";
            $direction = "ASC";
            break;
          case "created_asc":
            $sort = "created";
            $direction = "ASC";
            break;
          case "created_desc":
            $sort = "created";
            $direction = "DESC";
            break;
          case "total_asc":
            $sort = "total";
            $direction = "ASC";
            break;
          case "total_desc":
            $sort = "total";
            $direction = "DESC";
            break;
          case "state":
            $sort = "order_state";
            $direction = "DESC";
            break;
          case "manual":
            $sort = "manual";
            $direction = "DESC";
            break;
          default:
            $sort = "ord.id";
            $direction = "DESC";
            break;
		}

    $query .= " ORDER BY $sort $direction LIMIT $offset, $orders_listing_per_page ";

		$orders = $this->query($query);

		return $orders;
	}

	function listPromotions($page=1,$sort='') {
		global $rugs_listing_per_page;

		$promos = $this->query("SELECT COUNT(*) as `how_much` FROM `promotions ");
		$this->pages = ceil($promos[0]['how_much'] / $rugs_listing_per_page);

		$page = intval($page);
		if ($page > $this->pages)
			$page = $this->pages;
		if ($page < 1)
			$page = 1;

		$offset = ($page-1)*$rugs_listing_per_page;

		switch ($sort) {
			default:
				$sort = "id";
				$direction = "DESC";
				break;
		}

    $promos = $this->query("SELECT * FROM `promotions` ORDER BY `$sort` $direction LIMIT $offset, $rugs_listing_per_page");

    return $promos;
	}

	function listVouchers($page=1,$sort='') {
		global $rugs_listing_per_page;

		$vouchers = $this->query("SELECT COUNT(*) as `how_much` FROM `vouchers ");
		$this->pages = ceil($promos[0]['how_much'] / $rugs_listing_per_page);

		$page = intval($page);
		if ($page > $this->pages)
			$page = $this->pages;
		if ($page < 1)
			$page = 1;

		$offset = ($page-1)*$rugs_listing_per_page;

		switch ($sort) {
			default:
				$sort = "id";
				$direction = "DESC";
				break;
		}

    $vouchers = $this->query("SELECT * FROM `vouchers` ORDER BY `$sort` $direction LIMIT $offset, $rugs_listing_per_page");

    return $vouchers;
	}

	function getStats() {
		$stats = array();
    $total_stock_query      = "SELECT sum(stock) AS output FROM rugs_stock
                               WHERE active = '1'
                                 AND rug_id IN
                                (SELECT id FROM rugs WHERE active='1' and deleted ='0') ;";
    $total_cost_query       = "SELECT sum(cost_price * stock) AS output FROM rugs_stock
                               WHERE active = '1'
                                 AND rug_id IN
                                (SELECT id FROM rugs WHERE active='1' and deleted ='0') ;";
    $total_price_query      = "SELECT sum(price * stock) AS output FROM rugs_stock
                               WHERE active = '1'
                                 AND rug_id IN
                                (SELECT id FROM rugs WHERE active='1' and deleted ='0') ;";
    $total_sale_stock_query = "SELECT sum(stock) AS output FROM rugs_stock
                               WHERE (   discount_price IS NOT NULL
                                      OR discount_price_override IS NOT NULL )
                                 AND active = '1'
                                 AND rug_id IN
                                (SELECT id FROM rugs WHERE active='1' and deleted ='0') ;";
		$total_sale_cost_query = "SELECT sum(cost_price * stock) AS output FROM rugs_stock
                               WHERE (   discount_price IS NOT NULL
                                      OR discount_price_override IS NOT NULL )
                                 AND active = '1'
                                 AND rug_id IN
                                (SELECT id FROM rugs WHERE active='1' and deleted ='0') ;";
    $total_sale_price_query = "SELECT sum( IF(discount_price IS NULL OR discount_price_override IS NULL,
                                           COALESCE(discount_price_override, discount_price),
                                  LEAST(discount_price, discount_price_override)) * stock) AS output FROM rugs_stock
                               WHERE (   discount_price IS NOT NULL
                                      OR discount_price_override IS NOT NULL )
                                 AND active = '1'
                                 AND rug_id IN
                                (SELECT id FROM rugs WHERE active='1' and deleted ='0') ;";

    $total_stock_result      = $this-&gt;query($total_stock_query);
    $total_cost_result       = $this-&gt;query($total_cost_query);
    $total_price_result      = $this-&gt;query($total_price_query);
    $total_sale_stock_result = $this-&gt;query($total_sale_stock_query);
    $total_sale_cost_result  = $this-&gt;query($total_sale_cost_query);	
    $total_sale_price_result = $this-&gt;query($total_sale_price_query);

    $stats['total_stock'] = $total_stock_result[0]['output'];
    $stats['total_cost']  = '&pound; '. number_format($total_cost_result[0]['output'], 2);
    $stats['total_price'] = '&pound; '. number_format($total_price_result[0]['output'], 2);
    $stats['total_sale_stock'] = $total_sale_stock_result[0]['output'];
	  $stats['total_sale_cost']  = '&pound; '. number_format($total_sale_cost_result[0]['output'], 2);
    $stats['total_sale_price'] = '&pound; '. number_format($total_sale_price_result[0]['output'], 2);

		return $stats;
	}

  function findVoucher($code, $rugs) {

    $rug_list = array();
    foreach ($rugs as $rug)
      $rug_list[] = $rug['id'];

    $rug_criteria = '('.implode(',', $rug_list).')';
    echo $rug_criteria;

    // check the validity of a voucher code
    $sql = "SELECT * FROM vouchers WHERE voucher_code = '$code' ";
    $result = $this-&gt;query($sql);

    if ( count($result) &gt; 0 ) {
      // now check for rug validity
      $rug_sql = stripslashes($result[0]['criteria']);
      echo $rug_sql;

      $rug_result = $this-&gt;query($rug_sql);

      $rugs = array();
      foreach ( $rug_result as $rug )
        $rugs[] = $rug['id'];

      $rug_found = false;
      foreach ( $rug_list as $rug )
      {
        if ( in_array($rug, $rugs) ) {
          $rug_found = true;
        }
      }

      if ( $rug_found )
        return($result[0]['id']);
      else
        return 0;

    }
    else
      return 0;


  }
}
?&gt;


Problem is, I think that links back to previous code?

From a starting point I would look at the query and run some explain plans(http://dev.mysql.com/doc/refman/5.0/en/explain.html) in MySQL workbench(http://dev.mysql.com/downloads/gui-tools/5.0.html) or similar to find out the actual cost in MySQL terms for what you are trying to do. Basically capturing the errant behaviour outside of PHP and seeing if removing the column subqueries helps etc. I work purely in Oracle now and it handles column sub queries etc very well but MySQL may be converting it to something badly( such as a greedy join ) as when I last worked in it MySQL was far less optimised for that sort of work and more prone to those types of course of action. Large join executions can cause large disk usage for temp tables and cause slowness due to disc IO.

So things you need to try after putting on your CSI lab outfit on are:-

  1. Echo the better and bad query out.
  2. Switch to a tool suited for the job and run them through explain plans to get the analysis.
  3. See if it is reproducable outside of PHP.
  4. If it is reproducable try to isolate what it causing the problem by removing and re-adding parts of the query.

If this gives you the knowledge required to eliminate PHP as the problem then it is time to try some things out; The only thing I see here that would give PHP a head ache is the lack of limit clause and being given a large amount of data back to later process.

  1. If you are lucky it is something basically like missing indexes which are easy to add.

  2. Get to know the MySQL configuration such as the query cache engine http://dev.mysql.com/doc/refman/5.5/en/query-cache.html and how to configure it. If the data is fairly static the high cost can be paid less frequently.

  3. Try the MySQL forums with the un PHPfied raw queries explaining the size of the data in each table being used and give your explain plans. They have a far greater level of expertise in MySQL nuances.

  4. If you run out of options as some things databases just do not like look at technologies such as Solr which are free and built specific for search. Basically you give it a way of looking at you MySQL via a view and something it can use an updated date marker to do partial imports, or you can even create a flat table in MySQL for search and replicate to that on update as an in between process as that can eliminate the costly parts. More server space will be used but the IO may be cut from bad explain plans from every search which is the real enemy. Basically create a table mimicking the original table but has the extra columns then select from one into the other with http://dev.mysql.com/doc/refman/5.0/en/ansi-diff-select-into-table.html.

Minor question is there a limit clause? I cannot see one but I am not that sure it has any impact on this problem.

I hope this puts some pointers as it is fairly hypothetical from my past experience assuming you have a development environment similar to he setup of the the live site to play with. The code is bad but however you compose it the query will likely end up the same to get the same results so it is best to start there and go through a process of elimination.

Managed to print the SQL string being used to query the database. Below shows what is being sent to the database when doing a blank search. We feel this should basically return all products that are active and not bother with the highlighted parts. Is it just a case of making a function that unsets these conditions if the search is blank/refinements are not set? Would this speed up a ‘browse all’ search? We’d also probably look at somehow establishings “actual_price” before-hand so it doesn’t need the conditional statements to work it out.

Any suggestions/comments?

SELECT DISTINCT rugs.id as id, (SELECT min((IF(price IS NULL OR discount_price IS NULL, COALESCE(price, discount_price), LEAST(price, discount_price)))) FROM rugs_stock WHERE rugs_stock.rug_id = rugs.id) as actual_price FROM rugs, rug_types, rug_fabrics, rug_patterns, rug_subtypes, rug_shape, rug_designer, rug_designnames, rug_colours as colours1, rug_colours as colours2, rug_colours as colours3, rugs_stock as stock WHERE rugs.active = ‘1’ AND rugs.type = rug_types.id AND rugs.subtype = rug_subtypes.id AND rugs.fabric = rug_fabrics.id AND rugs.pattern = rug_patterns.id AND rugs.shape = rug_shape.id AND rugs.designer = rug_designer.id AND rugs.design_name = rug_designnames.id AND rugs.colour1 = colours1.id AND rugs.colour2 = colours2.id AND rugs.colour3 = colours3.id AND rugs.id = stock.rug_id AND stock.length>=‘0’ AND stock.length<=‘9’ AND stock.width>=‘0’ AND stock.width<=‘9’ ORDER BY RAND(1582782778) LIMIT 0, 10

that… looks like they’re doing a lot of WHERE clauses instead of proper JOIN conditions…

Yeah, what a mess.

Is the Rand() necessary? That alone will slow you down massively… God… where to even begin