SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 32
  1. #1
    SitePoint Member
    Join Date
    Dec 2012
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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 Code:
    <?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)>)
        
    $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);

  2. #2
    Non-Member
    Join Date
    Oct 2007
    Posts
    363
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    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...

  3. #3
    SitePoint Member
    Join Date
    Dec 2012
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

    PHP Code:
    <?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'])>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`>='0'";
                
    $pairs[] = "`width`>='0'";
            }

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

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

            if(isset(
    $conditions['price']) && $conditions['price'] != "") {
                
    $conditions['price'] = split("-"$conditions['price']);
                if(
    count($conditions['price']) == 2) {
                    
    $pairs[] = "`price`>='".floatval($conditions['price'][0])."'";
                    
    $pairs[] = "`price`<='".floatval($conditions['price'][1])."'";
                }
                else
                    
    $pairs[] = "`price`>='".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'])>0) {
                
    $sql .= " rugs.type = '".intval($conditions['type'])."' AND  ";
            }
            if(
    intval($conditions['subtype'])>0) {
                
    $sql .= " rugs.subtype = '".intval($conditions['subtype'])."' AND  ";
            }
            if(
    intval($conditions['pattern'])>0) {
                
    $sql .= " rugs.pattern = '".intval($conditions['pattern'])."' AND ";
            }
            if(
    intval($conditions['fabric'])>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'])>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 > 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);

  4. #4
    Non-Member
    Join Date
    Oct 2007
    Posts
    363
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Mal1 View Post
    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?

    PHP Code:
    <?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'])>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`>='0'";
                
    $pairs[] = "`width`>='0'";
            }

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

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

            if(isset(
    $conditions['price']) && $conditions['price'] != "") {
                
    $conditions['price'] = split("-"$conditions['price']);
                if(
    count($conditions['price']) == 2) {
                    
    $pairs[] = "`price`>='".floatval($conditions['price'][0])."'";
                    
    $pairs[] = "`price`<='".floatval($conditions['price'][1])."'";
                }
                else
                    
    $pairs[] = "`price`>='".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'])>0) {
                
    $sql .= " rugs.type = '".intval($conditions['type'])."' AND  ";
            }
            if(
    intval($conditions['subtype'])>0) {
                
    $sql .= " rugs.subtype = '".intval($conditions['subtype'])."' AND  ";
            }
            if(
    intval($conditions['pattern'])>0) {
                
    $sql .= " rugs.pattern = '".intval($conditions['pattern'])."' AND ";
            }
            if(
    intval($conditions['fabric'])>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'])>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 > 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.

  5. #5
    Non-Member
    Join Date
    Oct 2007
    Posts
    363
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    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:

    http://www.amazon.co.uk/Refactoring-.../dp/0201485672
    http://www.amazon.co.uk/Working-Effe...4732562&sr=1-1

    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).

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,192
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    One thing that sticks out to me is that when $conditions['keywords'] exists that filters are applied using regexp.

    Code PHP:
      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.
    The only code I hate more than my own is everyone else's.

  7. #7
    Non-Member
    Join Date
    Oct 2007
    Posts
    363
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    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:
    PHP Code:
      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...

  8. #8
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,314
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)
    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.
    "First make it work. Then make it better."

  9. #9
    Non-Member
    Join Date
    Oct 2007
    Posts
    363
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Jeff Mott View Post
    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.

  10. #10
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,314
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)
    Yes, I agree. Version control is one of the basics that you should always, always, always use.
    "First make it work. Then make it better."

  11. #11
    Non-Member
    Join Date
    Oct 2007
    Posts
    363
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Jeff Mott View Post
    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.

  12. #12
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,314
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by aaarrrggh View Post
    Mercurial and GIT are the two common version control systems used today.
    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.
    "First make it work. Then make it better."

  13. #13
    Non-Member
    Join Date
    Oct 2007
    Posts
    363
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Jeff Mott View Post
    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.

  14. #14
    SitePoint Member
    Join Date
    Dec 2012
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  15. #15
    SitePoint Member
    Join Date
    Dec 2012
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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
    PHP Code:
    <?php
    class Database {
        var 
    $pages$error$query_count;

        function 
    Database($db_user$db_pass$db_name$db_host) {
            
    $this->query_count 0;
            
    $this->error 0;

            if (!
    mysql_connect($db_host$db_user$db_pass) || !mysql_select_db($db_name))
                
    $this->error 1;
        }

        function 
    query($sql_query) {
            
    //echo $sql_query.'<br />';
            
    $this->query_count++;
            
    $result mysql_query($sql_query);

            
    $rows = array();
            if(@
    mysql_num_rows($result) > 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 => $value)
          
    // do not quote NULLs
          
    if ( $value == "NULL" )
            
    $pairs[] = "`$field`=".$this->escape($value);
          else
                
    $pairs[] = "`$field`='".$this->escape($value)."'";

            
    $sql "UPDATE `$table` SET ".join(","$pairs)." WHERE `id`='{$fields['id']}'";
        
    //    echo $sql;
    //    die();
    //        print_r($fields);
    //        die();
            
    $this->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->query("SELECT `id` FROM `rugs` WHERE `active`='1' AND `deleted`='0' ORDER BY RAND()");

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

            
    $page intval($page);
            if (
    $page $this->pages)
                
    $page $this->pages;
            if (
    $page 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->query("SELECT * FROM `rugs` WHERE ".join(" OR ", $ids));
            
    $rugs=array();
            foreach(
    $ids as $id) {
                
    $rugs=array_merge($rugs$this->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->query("SELECT LOWER(name) as name FROM rug_search_ignore_words WHERE id <> 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->query($sql);

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

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

        
    //echo "rug_count: ".count($rugs_all)."<br />";
        //echo "<br />---FULL---".$sql;
        
    $page_count $this->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->query($sql);
        
    $rugs = array();    
        foreach ( 
    $rugs_search as $rug ) {
            
    $tmp = new Rug($rug['id']);
            
            
    // apply promotions
            
    $tmp->applyPromotion();
            if(!isset(
    $_GET['admin-search']) OR (isset($_GET['admin-search']) && $_GET['admin-search'] == 0)){
            
                
    // eliminate rugs without any stock       
                
    if ( $tmp->useQuantity() ) {
                    
    $stock 0;
                    
    // look for active stock        
                    
    foreach ( $tmp->data['stock']->data as $stock => $values ) {
                        if ( 
    $values['active'] == '1' && $values['stock'] > ){
                            
    $stock += $values['stock'];
                        }
                    }
                    if ( 
    $stock ){
                        
    $rugs[] = $tmp->data;
                    }
                }else{
                    
    $rugs[] = $tmp->data;
                }
            }else{
                
    $rugs[] = $tmp->data;
            }

        }
        return array( 
    'this' => $rugs'all' => $rugs_all'sql' => $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->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 => $value)
                if (
    in_array($field$search_fields) && trim($value) != "")
                    if (
    $field == "created")
                        
    $pairs[] = "DATE(`$field`)='".$this->escape($value)."'";
                    else
                        
    $pairs[] = "`$field`='".$this->escape($value)."'";

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

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

            if (isset(
    $conditions['price'])) {
                
    $conditions['price'] = split("-"$conditions['price']);
                if (
    count($conditions['price']) == 2) {
                    
    $pairs[] = "`price`>='".intval($conditions['price'][0])."'";
                    
    $pairs[] = "`price`<='".intval($conditions['price'][1])."'";
                }
                else
                    
    $pairs[] = "`price`>='".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->query($total_stock_query);
        
    $total_cost_result       $this->query($total_cost_query);
        
    $total_price_result      $this->query($total_price_query);
        
    $total_sale_stock_result $this->query($total_sale_stock_query);
        
    $total_sale_cost_result  $this->query($total_sale_cost_query);    
        
    $total_sale_price_result $this->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->query($sql);

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

          
    $rug_result $this->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;

        
      }
    }
    ?>
    Then there's stock.class...

  16. #16
    SitePoint Member
    Join Date
    Dec 2012
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Narrowed the main culprit (time-wise) down to this small section of code within the database class:

    PHP Code:
        //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:

    PHP Code:
    <?php

    class Database {
        var 
    $pages$error$query_count;

        function 
    Database($db_user$db_pass$db_name$db_host) {
            
    $this->query_count 0;
            
    $this->error 0;

            if (!
    mysql_connect($db_host$db_user$db_pass) || !mysql_select_db($db_name))
                
    $this->error 1;
        }

        function 
    query($sql_query) {
            
    //echo $sql_query.'<br />';
            
    $this->query_count++;
            
    $result mysql_query($sql_query);

            
    $rows = array();
            if(@
    mysql_num_rows($result) > 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 => $value)
          
    // do not quote NULLs
          
    if ( $value == "NULL" )
            
    $pairs[] = "`$field`=".$this->escape($value);
          else
                
    $pairs[] = "`$field`='".$this->escape($value)."'";

            
    $sql "UPDATE `$table` SET ".join(","$pairs)." WHERE `id`='{$fields['id']}'";
        
    //    echo $sql;
    //    die();
    //        print_r($fields);
    //        die();
            
    $this->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->query("SELECT `id` FROM `rugs` WHERE `active`='1' AND `deleted`='0' ORDER BY RAND()");

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

            
    $page intval($page);
            if (
    $page $this->pages)
                
    $page $this->pages;
            if (
    $page 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->query("SELECT * FROM `rugs` WHERE ".join(" OR ", $ids));
            
    $rugs=array();
            foreach(
    $ids as $id) {
                
    $rugs=array_merge($rugs$this->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->query("SELECT LOWER(name) as name FROM rug_search_ignore_words WHERE id <> 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->query($sql);

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

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

        
    //echo "rug_count: ".count($rugs_all)."<br />";
        //echo "<br />---FULL---".$sql;
        
    $page_count $this->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->query($sql);
        
    $rugs = array();    
        foreach ( 
    $rugs_search as $rug ) {
            
    $tmp = new Rug($rug['id']);
            
    $_SESSION['end_time'] = microtime(true);
            
            
    // apply promotions
            
    $tmp->applyPromotion();
            if(!isset(
    $_GET['admin-search']) OR (isset($_GET['admin-search']) && $_GET['admin-search'] == 0)){
            
                
    // eliminate rugs without any stock       
                
    if ( $tmp->useQuantity() ) {
                    
    $stock 0;
                    
    // look for active stock        
                    
    foreach ( $tmp->data['stock']->data as $stock => $values ) {
                        if ( 
    $values['active'] == '1' && $values['stock'] > ){
                            
    $stock += $values['stock'];
                        }
                    }
                    if ( 
    $stock ){
                        
    $rugs[] = $tmp->data;
                    }
                }else{
                    
    $rugs[] = $tmp->data;
                }
            }else{
                
    $rugs[] = $tmp->data;
            }

        }
        return array( 
    'this' => $rugs'all' => $rugs_all'sql' => $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->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 => $value)
                if (
    in_array($field$search_fields) && trim($value) != "")
                    if (
    $field == "created")
                        
    $pairs[] = "DATE(`$field`)='".$this->escape($value)."'";
                    else
                        
    $pairs[] = "`$field`='".$this->escape($value)."'";

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

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

            if (isset(
    $conditions['price'])) {
                
    $conditions['price'] = split("-"$conditions['price']);
                if (
    count($conditions['price']) == 2) {
                    
    $pairs[] = "`price`>='".intval($conditions['price'][0])."'";
                    
    $pairs[] = "`price`<='".intval($conditions['price'][1])."'";
                }
                else
                    
    $pairs[] = "`price`>='".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->query($total_stock_query);
        
    $total_cost_result       $this->query($total_cost_query);
        
    $total_price_result      $this->query($total_price_query);
        
    $total_sale_stock_result $this->query($total_sale_stock_query);
        
    $total_sale_cost_result  $this->query($total_sale_cost_query);    
        
    $total_sale_price_result $this->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->query($sql);

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

          
    $rug_result $this->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;

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

  17. #17
    SitePoint Member
    Join Date
    Feb 2009
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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/...nto-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.

  18. #18
    SitePoint Member
    Join Date
    Dec 2012
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  19. #19
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    that... looks like they're doing a lot of WHERE clauses instead of proper JOIN conditions...
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  20. #20
    Non-Member
    Join Date
    Oct 2007
    Posts
    363
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    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

  21. #21
    SitePoint Member
    Join Date
    Dec 2012
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Will JOIN improve performance?

    Quote Originally Posted by aaarrrggh View Post
    Yeah, what a mess.

    Is the Rand() necessary? That alone will slow you down massively... God... where to even begin
    The Rand was put in so when someone does a search they're not getting the same order of results back every time which would get boring if they come back to the site (going this slow it's doubtful!)... it would be good to be able to randomise the results but no, I guess it's not really needed.

    I did remove that section of code the other day though and it didn't look it sped things up much

  22. #22
    SitePoint Member
    Join Date
    Dec 2012
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $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 "
    ;

    $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 it's the Join/Where issue then the problem is located here? ^^

  23. #23
    SitePoint Member
    Join Date
    Dec 2012
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok... pulling my hair out here.

    I know it's not 100% possible to tell but roughly how long do you think it would take to fix this to an acceptable level? And what would you recommend as the best way to go around finding a freelance programmer. I'd really prefer to be able to sit in the same room as them while they work so there's no communication issues and because we would be able to assist in making sense of the messy code as we know the website and functions. So ideally it would be Glasgow based.

  24. #24
    SitePoint Wizard bronze trophy Jeff Mott's Avatar
    Join Date
    Jul 2009
    Posts
    1,314
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)
    @aaarrrggh hit the nail on the head... twice. The main culprit is the "order by rand". I re-created your schema on my machine and filled it with dummy data so that I could actually test changes to the query. Just deleting order by rand yielded a 97% speed boost.
    "First make it work. Then make it better."

  25. #25
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,072
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Jeff Mott View Post
    @aaarrrggh hit the nail on the head... twice. The main culprit is the "order by rand". I re-created your schema on my machine and filled it with dummy data so that I could actually test changes to the query. Just deleting order by rand yielded a 97% speed boost.
    If you need x number of random rows from the result set, do the queries as normal but don't use an ORDER BY RAND, instead, once you've got the result set into an array, use array_rand() to pick the number of random entries needed. If you need all the results but in a random order then use shuffle() on the result set.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •