I’ve got a website that I’ve taken over the management of and when running a search for products it is excruciatingly slow! It’s not so bad when there are quite a few search options selected but for a blank search it takes around 12-15 seconds to load the search results (and then does so when you click next page, previous page which doesn’t seem right).
I’m no expert at all, I can normally just about work things out by myself when managing the websites (there’s another site which is 10 times as fast with a similar number of products). The only difference I can see between the two is that on the slow site there is a stock table set us as the one “product” can have various sizes and different prices associated to these prices.
Here’s some of the code that runs in the search, I’d greatly appreciate it if somebody could have a look and if anything sticks out as wrong give me a nudge in the right direction. Thanks…
<?php
if ($conditions['id'] == "")
unset($conditions['id']);
// handle id/keyword searching
if (is_numeric($conditions['keywords']))
{
$conditions['id'] = intval($conditions['keywords']);
unset($conditions['keywords']);
}
if ($_GET['debug'] == 1)
print_r($conditions);
// $sql = "SELECT main_picture, rugs.id as id, length, width, shape,
// rugs.type as type, rugs.subtype, fabric, pattern,
// colour1, colour2, colour3, price, discount_price,
// rugs.stock, rugs.active, rugs.views, rugs.sold,
// rug_types.name as type_name,
// rug_types.description as type_description,
// rug_fabrics.name as fabric_name,
// rug_patterns.name as pattern_name,
// rug_subtypes.name as subtype_name,
// rug_shape.name as shape,
// rug_designer.name as rug_designer,
// rug_designnames.name as design_name,
// colours1.name as colour1_name,
// colours2.name as colour2_name,
// colours3.name as colour3_name,
// IF(price IS NULL OR discount_price IS NULL,
// COALESCE(price, discount_price), LEAST(price, discount_price)) as actual_price
// FROM rugs, rug_types, rug_fabrics, rug_patterns, rug_subtypes,
// rug_shape, rug_designer, rug_designnames,
// rug_colours as colours1, rug_colours as colours2, rug_colours as colours3 ";
// $sql .= "WHERE rugs.type = rug_types.id
// AND rugs.subtype = rug_subtypes.id
// AND rugs.fabric = rug_fabrics.id
// AND rugs.pattern = rug_patterns.id
// AND rugs.shape = rug_shape.id
// AND rugs.designer = rug_designer.id
// AND rugs.design_name = rug_designnames.id
// AND rugs.colour1 = colours1.id
// AND rugs.colour2 = colours2.id
// AND rugs.colour3 = colours3.id
// AND ";
// $sql = "SELECT main_picture, rugs.id as id, shape,
// rugs.type as type, rugs.subtype, fabric, pattern,
// colour1, colour2, colour3,
// rugs.active, rugs.views,
// rug_types.name as type_name,
// rug_types.description as type_description,
// rug_fabrics.name as fabric_name,
// rug_patterns.name as pattern_name,
// rug_subtypes.name as subtype_name,
// rug_shape.name as shape,
// rug_designer.name as rug_designer,
// rug_designnames.name as design_name,
// colours1.name as colour1_name,
// colours2.name as colour2_name,
// colours3.name as colour3_name
// FROM rugs, rug_types, rug_fabrics, rug_patterns, rug_subtypes,
// rug_shape, rug_designer, rug_designnames,
// rug_colours as colours1, rug_colours as colours2, rug_colours as colours3 ";
$agg = 'min';
if ($sort=='price_desc') $agg = 'max';
if ($sort=='price_asc') $agg = 'min';
$base_sql = "SELECT DISTINCT rugs.id as id,
(SELECT $agg((IF(price IS NULL OR discount_price IS NULL,
COALESCE(price, discount_price), LEAST(price, discount_price)))) FROM rugs_stock WHERE rugs_stock.rug_id = rugs.id) as actual_price
FROM rugs, rug_types, rug_fabrics, rug_patterns, rug_subtypes,
rug_shape, rug_designer, rug_designnames,
rug_colours as colours1, rug_colours as colours2, rug_colours as colours3, rugs_stock as stock ";
//changed "as count" to "as actual_price" to solve sort by price problem - may have problems elsewhere if so change back
$count_sql = "SELECT COUNT(DISTINCT rugs.id,
(SELECT $agg((IF(price IS NULL OR discount_price IS NULL,
COALESCE(price, discount_price), LEAST(price, discount_price)))) FROM rugs_stock WHERE rugs_stock.rug_id = rugs.id)) as actual_price
FROM rugs, rug_types, rug_fabrics, rug_patterns, rug_subtypes,
rug_shape, rug_designer, rug_designnames,
rug_colours as colours1, rug_colours as colours2, rug_colours as colours3, rugs_stock as stock ";
$sql .= "WHERE ";
if ( !isset($_GET['admin-search']) && empty($_GET['admin-search']) )
$sql .= " rugs.active = '1' AND ";
$sql .= " rugs.type = rug_types.id
AND rugs.subtype = rug_subtypes.id
AND rugs.fabric = rug_fabrics.id
AND rugs.pattern = rug_patterns.id
AND rugs.shape = rug_shape.id
AND rugs.designer = rug_designer.id
AND rugs.design_name = rug_designnames.id
AND rugs.colour1 = colours1.id
AND rugs.colour2 = colours2.id
AND rugs.colour3 = colours3.id
AND rugs.id = stock.rug_id
";
if (strlen($conditions['keywords']) > 0)
{
// do keyword stuff
$keywords = split(" ", $conditions['keywords']);
// clean any ignore words
foreach ($keywords as $key => $keyword)
{
if (in_array(strtolower($keyword), $ignore_list))
unset($keywords[$key]);
}
$key_tables = array('rug_designer', 'rug_designnames', 'rug_fabrics', 'rug_patterns', 'rug_shape',
'rug_types', 'rug_subtypes', 'colours1', 'colours2', 'colours3');
$sql_array = array();
foreach ($keywords as $keyword)
{
$keyword_array = array();
foreach ($key_tables as $table)
{
//$keyword_array[] = $table . ".name LIKE LOWER('" . strtolower($keyword) . "%') ";
$keyword_array[] = $table . ".name REGEXP '[[:<:]]".$keyword."[[:>:]]' ";
}
// do the same for mod_trad field
$keyword_array[] = "rugs.mod_trad LIKE LOWER('". strtolower($keyword) ."%') ";
// implode the keyword likes with 'OR'
$sql_array[] = "( " . implode(' OR ', $keyword_array) . ") ";
}
$sql .= " AND ( ";
// implode the phrases with 'AND'
$sql .= implode(' AND ', $sql_array);
$sql .= ") ";
}
else
{
if (intval($conditions['type']) > 0)
{
$sql .= " AND rugs.type = '" . intval($conditions['type']) . "' ";
}
if (intval($conditions['subtype']) > 0)
{
$sql .= " AND rugs.subtype = '" . intval($conditions['subtype']) . "' ";
}
if (intval($conditions['design_name']) > 0)
{
$sql .= " AND rugs.design_name = '" . intval($conditions['design_name']) . "' ";
}
if (intval($conditions['designer']) > 0)
{
$sql .= " AND rugs.designer = '" . intval($conditions['designer']) . "' ";
}
if (intval($conditions['pattern']) > 0)
{
$sql .= " AND rugs.pattern = '" . intval($conditions['pattern']) . "' ";
}
if (intval($conditions['fabric']) > 0)
{
$sql .= " AND rugs.fabric = '" . intval($conditions['fabric']) . "' ";
}
if (intval($conditions['shape']) > 0)
{
$sql .= " AND rugs.shape = '" . intval($conditions['shape']) . "' ";
}
if(is_numeric($conditions['kids']))
{
$sql .= " AND rugs.kids = '".$conditions['kids']."' ";
}
if ( $conditions['mod_trad'] != 'Any' )
{
if ($conditions['mod_trad'] == 'Modern')
{
$sql .= " AND ( rugs.mod_trad = 'Modern' OR rugs.mod_trad = 'Either' ) ";
}
else if ($conditions['mod_trad'] == 'Traditional')
{
$sql .= " AND ( rugs.mod_trad = 'Traditional' OR rugs.mod_trad = 'Either' ) ";
}
else if ($conditions['mod_trad'] == 'Either')
{
$sql .= " AND rugs.mod_trad = 'Either' ";
}
}
if(intval($conditions['colour'])>0) {
$colour = intval($conditions['colour']);
$sql .= " AND ( rugs.colour1 = '$colour' or rugs.colour2 = '$colour' or rugs.colour3 = '$colour' ) ";
}
else
{
if (count($conditions['colours']) > 0)
{
foreach ($conditions['colours'] as $colour)
{
$sql .= " AND ( rugs.colour1 = '" . $colour . "' OR
rugs.colour2 = '" . $colour . "' OR
rugs.colour3 = '" . $colour . "' ) ";
}
}
}
if (strlen($conditions['price_from']) > 0)
{
$conditions['price'] = $conditions['price_from'];
if (isset($conditions['price_to']))
$conditions['price'] = $conditions['price_from'] . "-" . $conditions['price_to'];
}
if (isset($conditions['price']) && $conditions['price'] != "")
{
$conditions['price'] = split("-", $conditions['price']);
if (count($conditions['price']) == 2)
{
$pairs[] = "IF(stock.price IS NULL OR stock.discount_price IS NULL,
COALESCE(stock.price, stock.discount_price),
LEAST(stock.price, stock.discount_price))>='". floatval($conditions['price'][0]) . "'";
$pairs[] = "IF(stock.price IS NULL OR stock.discount_price IS NULL,
COALESCE(stock.price, stock.discount_price),
LEAST(stock.price, stock.discount_price))<='". floatval($conditions['price'][1]) . "'";
}
else {
$pairs[] = "IF(stock.price IS NULL OR stock.discount_price IS NULL,
COALESCE(stock.price, stock.discount_price),
LEAST(stock.price, stock.discount_price))>='". floatval($conditions['price'][0]) . "'";
}
}
// if ($conditions['action2'] == 'search')
// {
// if ($conditions['size'] == 'm')
// {
if ($conditions['length_m_from'] == "" and $conditions['length_m_to'] == "")
{
$conditions['length_m_from'] = 0;
$conditions['length_m_to'] = 9;
}
if ($conditions['width_m_from'] == "" and $conditions['width_m_to'] == "")
{
$conditions['width_m_from'] = 0;
$conditions['width_m_to'] = 9;
}
$conditions['length'] = $conditions['length_m_from'] . "-" . $conditions['length_m_to'];
$conditions['width'] = $conditions['width_m_from'] . "-" . $conditions['width_m_to'];
// }
// else
// {
// if ($conditions['length_ft_from'] == "" and $conditions['length_ft_to'] == "")
// {
// $conditions['length_ft_from'] = 0;
// $conditions['length_ft_to'] = 9;
// }
// if ($conditions['width_ft_from'] == "" and $conditions['width_ft_to'] == "")
// {
// $conditions['width_ft_from'] = 0;
// $conditions['width_ft_to'] = 9;
// }
// // imperial/metric conversion
// $m_conv = 0.3048;
// $i_conv = 0.0254;
//
// $imperial_length_from = $m_conv * intval($conditions['length_ft_from']);
// $imperial_length_from_inches = $i_conv * intval($conditions['inch_length_from']);
// $length_from = $imperial_length_from + $imperial_length_from_inches;
// $imperial_length_to = $m_conv * intval($conditions['length_ft_to']);
// $imperial_length_to_inches = $i_conv * intval($conditions['inch_length_to']);
// $length_to = $imperial_length_to + $imperial_length_to_inches;
// $conditions['length'] = $length_from . "-" . $length_to;
//
// $imperial_width_from = $m_conv * intval($conditions['width_ft_from']);
// $imperial_width_from_inches = $i_conv * intval($conditions['inch_width_from']);
// $width_from = $imperial_width_from + $imperial_width_from_inches;
// $imperial_width_to = $m_conv * intval($conditions['width_ft_to']);
// $imperial_width_to_inches = $i_conv * intval($conditions['inch_width_to']);
// $width_to = $imperial_width_to + $imperial_width_to_inches;
// $conditions['width'] = $width_from . "-" . $width_to;
// }
// }
if (!isset($conditions['length']) or !isset($conditions['width'])) {
$pairs[] = "stock.length>='0'";
$pairs[] = "stock.width>='0'";
$pairs[] = 'stock.stock > 0';
}
if (isset($conditions['length']) && $conditions['length'] != "")
{
$conditions['length'] = split("-", $conditions['length']);
if (count($conditions['length']) == 2)
{
$pairs[] = "stock.length>='" . floatval($conditions['length'][0]) . "'";
$pairs[] = "stock.length<='" . floatval($conditions['length'][1]) . "'";
}
else
$pairs[] = "stock.length>='" . floatval($conditions['length'][0]) . "'";
}
if (isset($conditions['width']) && $conditions['width'] != "")
{
$conditions['width'] = split("-", $conditions['width']);
if (count($conditions['width']) == 2)
{
$pairs[] = "stock.width>='" . floatval($conditions['width'][0]) . "'";
$pairs[] = "stock.width<='" . floatval($conditions['width'][1]) . "'";
}
else
$pairs[] = "stock.width>='" . floatval($conditions['width'][0]) . "'";
}
if (isset($conditions['id']) && !isset($conditions['admin-search']))
{
$sql .= " AND rugs.id = '" . intval($conditions['id']) . "' ";
}
if ( count($pairs)>0 )
$sql .= " AND ".join(" AND ", $pairs);
} // end of keyword skip
// search via admin area will show zero stock and inactive
// if ( isset($_GET['admin-search']) && !empty($_GET['admin-search']) && $_GET['admin-search'] == 1 )
// $sql .= " AND rugs.deleted=0 ";
// else
// $sql .= " AND rugs.active=1 AND rugs.deleted=0 AND (rugs.stock > 0 OR rugs.stock_override = 1) ";
//
if(!is_numeric($_GET['rand_seed'])) $_GET['rand_seed'] = intval(rand());
switch($sort){
case "price_asc":
$sort = " ORDER BY actual_price ASC, RAND(".intval($_GET['rand_seed']).")";
break;
case "price_desc":
$sort = " ORDER BY actual_price DESC, RAND(".intval($_GET['rand_seed']).")";
break;
case "created":
$sort = " ORDER BY rugs.id DESC, RAND(".intval($_GET['rand_seed']).")";
break;
case "oldest":
$sort = " ORDER by rugs.id ASC, RAND(".intval($_GET['rand_seed']).")";
break;
case "special_offer":
$sort = " ORDER by discount_price DESC, price DESC, RAND(".intval($_GET['rand_seed']).")";
break;
default:
if($conditions['mod_trad']){
$sort = " ORDER BY FIELD(rugs.mod_trad, 'Modern', 'Traditional', 'Either'), RAND(".intval($_GET['rand_seed']).")";
}else{
$sort = " ORDER BY RAND(".intval($_GET['rand_seed']).")";
}
break;
}
$sql .= $sort;
// do base and count sql statements
$count_sql .= $sql;
$sql = $base_sql.$sql;
//echo $sql;
if ($_GET['debug'] == 1)
echo print_r($conditions);