Slow mySQL query

[zink]

Hi

My results are taking about 45 seconds to come back can someone help me?

i am using full-text search to query a DB that only has 6000 products so shouldnt be this slow.

I dont think it's the query itself as I wrapped a timer around it and it says it completes in 0.8 seconds. I then wrapped the timer around the loop in the function and this is where the time builds up!

Page call:

 dbConnect();

$prods = getProducts("SELECT * FROM products WHERE MATCH(category, name) AGAINST('+(jean jeans) +(mens men\'s mans man\'s \"designer men\") -(sweatshirt beanie shirt t-shirt)' IN BOOLEAN MODE) ORDER BY $sType $sDirection");

$num = count($prods);

$results_per_page = 30;
if(!isset($_GET['p'])) $page_numer = 0;
else $page_numer = (int)$_GET['p'];
if(!is_int($page_numer)) die('exploit attempt, page is not a number');

if($num == 0) echo "<b><center>There are currently no products in this section</center></b>";
else {
  $x = $results_per_page * ($page_numer - 1);
  for($i=$x;$i<$results_per_page;$i++) {
    if($prods[$i]['imageurl'] == null) echo "<a href='{$prods[$i]['url']}' target='_blank' rel='nofollow'><img src='assets/images/blank.gif'
align='right' border='0' width='100' height='100' alt='{$prods[$i]['name']}'/></a><b><a href='{$prods[$i]['url']}' target='_blank'
rel='nofollow'>{$prods[$i]['name']}</a></b><br/>{$prods[$i]['description']} <a href='{$prods[$i]['url']}' target='_blank' rel='nofollow'>Read
more</a><br/><br/><b>Price:</b> £{$prods[$i]['price']}<br clear='all'><br/><br/>";
    else echo "<a href='{$prods[$i]['url']}' target='_blank' rel='nofollow'><img src='{$prods[$i]['imageurl']}' align='right' border='0' width='125'
height='123' alt='{$prods[$i]['name']}'/></a><b><a href='{$prods[$i]['url']}' target='_blank'
rel='nofollow'>{$prods[$i]['name']}</a></b><br/>{$prods[$i]['description']} <a href='{$prods[$i]['url']}' target='_blank' rel='nofollow'>Read
more</a><br/><br/><b>Price:</b> £{$prods[$i]['price']}<br clear='all'><br/><br/>";    
  }    
}
echo "Page: ";

$number_of_pages = ceil($num / $results_per_page);
for($i=1;$i<$number_of_pages;$i++) {
  if($i == $page_numer) echo " $i | ";
  else echo "<a href='?p=$i'>$i</a> |";
} 

Function:

function getProducts($query){
  $result = mysql_query($query);
  $num = mysql_numrows($result);
  $i=0;
  $return = array();
  while ($i < $num) {
    $return[$i]['name'] = mysql_result($result,$i,"name");
    $return[$i]['description'] = mysql_result($result,$i,"description");
    $return[$i]['url'] = mysql_result($result,$i,"url");
    $return[$i]['imageurl'] = mysql_result($result,$i,"imageurl");
    $return[$i]['price'] = mysql_result($result,$i,"price");
    $return[$i]['price'] = number_format($return['price'], 2);
    //SWITCH HYPHENS BACK
    $return[$i]['name'] = str_replace("HYPHEN", "-", $return[$i]['name']);
    $return[$i]['description'] = str_replace("HYPHEN", "-", $return[$i]['description']);
    $return[$i]['url'] = str_replace("HYPHEN", "-", $return[$i]['url']);
    $return[$i]['imageurl'] = str_replace("HYPHEN", "-", $return[$i]['imageurl']);
    $return[$i]['price'] = str_replace("HYPHEN", "-", $return[$i]['price']);
    $return[$i]['description'] = summarize_search_result($return[$i]['description'], 30);
    // check image url and store
   $var = @fopen("{$return[$i]['imageurl']}","r");
    if ($var) $return[$i]['imageurl'] = $return[$i]['imageurl'];
    else $return[$i]['imageurl'] = null;
    $i++;
  }
  return $return;
} 

Thanks for your help!
-Zink

[markl999]

You really don't want to be fetching 6000 rows of data on every page just to show 30 of them. Use the LIMIT clause to just select the ones you need: LIMIT $page_numer*$results_per_page, $results_per_page for example.

To get the total number of rows either run another query that just does SELECT COUNT(*) AS total FROM .... to get the total or use SQL_CALC_FOUND_ROWS in the first query and it will get both the total number of rows and only those required for the current page.

[zink]

Hi Mark,

Thanks for the response, so you think thats getting 6000 rows is my problem?

so to implement what you are saying would this work by changing the following line to:

$prods = getProducts("SELECT SQL_CALC_FOUND_ROWS * FROM products WHERE MATCH(category, name) AGAINST('+(jean jeans) +(mens men\'s mans man\'s \"designer men\") -(sweatshirt beanie shirt t-shirt)' IN BOOLEAN MODE) ORDER BY $sType $sDirection"); 

[markl999]

You need to LIMIT the query, for example,

$offset = $page_numer*$results_per_page;
list($num, $prods) = getProducts("SELECT SQL_CALC_FOUND_ROWS * FROM products WHERE MATCH(category, name) AGAINST('+(jean jeans) +(mens men\'s mans man\'s \"designer men\") -(sweatshirt beanie shirt t-shirt)' IN BOOLEAN MODE) ORDER BY $sType $sDirection LIMIT $offset, $results_per_page"); 

Then change getProducts to look something like:

function getProducts($query){
  $result = mysql_query($query) or die(mysql_error());
  $result2 = mysql_query("SELECT FOUND_ROWS()") or die(mysql_error());
  $result2row = mysql_fetch_assoc($result2);
  $totalrows = $result2row['FOUND_ROWS()'];
  $num = mysql_num_rows($result);
  //rest of your code goes here
  //return the total number of rows and the rows of data for this page
  return array($totalrows, $return); 

You'll then no longer need the line:
$num = count($prods);

[zink]

Hey Mark,

Thanks mate this works excellently my query is down from 45 seconds to 1-1.5 seconds!!

Just noticed a wierd problem though... but I am pretty sure it was like this before your code.

http://www.designerclothesforless.co...-jeans.php?p=1 if you click to go on to page 2, 3 etc.. it doesn't display any products LOL

Any ideas?

Thanks for all your help BTW

Function:

function getProducts2($query){

$starttime = microtime();
$startarray = explode(" ", $starttime);
$starttime = $startarray[1] + $startarray[0];

$result = mysql_query($query) or die(mysql_error()); 
  $result2 = mysql_query("SELECT FOUND_ROWS()") or die(mysql_error()); 
  $result2row = mysql_fetch_assoc($result2); 
  $totalrows = $result2row['FOUND_ROWS()']; 
  $num = mysql_num_rows($result); 
  
  
  //rest of code goes here
    $i=0;
    $return = array();
    while ($i < $num) {
    $return[$i]['name'] = mysql_result($result,$i,"name");
    $return[$i]['description'] = mysql_result($result,$i,"description");
    $return[$i]['url'] = mysql_result($result,$i,"url");
    $return[$i]['imageurl'] = mysql_result($result,$i,"imageurl");
    $return[$i]['price'] = mysql_result($result,$i,"price");
    $return[$i]['price'] = number_format($return['price'], 2);
    //SWITCH HYPHENS BACK
    $return[$i]['name'] = str_replace("HYPHEN", "-", $return[$i]['name']);
    $return[$i]['description'] = str_replace("HYPHEN", "-", $return[$i]['description']);
    $return[$i]['url'] = str_replace("HYPHEN", "-", $return[$i]['url']);
    $return[$i]['imageurl'] = str_replace("HYPHEN", "-", $return[$i]['imageurl']);
    $return[$i]['price'] = str_replace("HYPHEN", "-", $return[$i]['price']);
    $return[$i]['description'] = summarize_search_result($return[$i]['description'], 30);
    // check image url and store
    $var = @fopen("{$return[$i]['imageurl']}","r");
    if ($var) $return[$i]['imageurl'] = $return[$i]['imageurl'];
    else $return[$i]['imageurl'] = null;
    $i++;
  }

 
$endtime = microtime();
$endarray = explode(" ", $endtime);
$endtime = $endarray[1] + $endarray[0];
$totaltime = $endtime - $starttime; 
$totaltime = round($totaltime,5);
echo "<b>$i.This page loaded in $totaltime seconds.</b><br><br>";


 //return the total number of rows and the rows of data for this page 
  return array($totalrows, $return); 
  
} 

Page:

dbConnect();

$results_per_page = 30;
if(!isset($_GET['p'])) $page_numer = 0;
else $page_numer = (int)$_GET['p'];
if(!is_int($page_numer)) die('exploit attempt, page is not a number');

$offset = $page_numer*$results_per_page; 
list($num, $prods) = getProducts2("SELECT SQL_CALC_FOUND_ROWS * FROM products WHERE MATCH(category, name) AGAINST('+(jean jeans) +(mens men\'s mans man\'s \"designer men\") -(sweatshirt beanie shirt t-shirt)' IN BOOLEAN MODE) ORDER BY $sType $sDirection LIMIT $offset, $results_per_page"); 


if($num == 0) echo "<b><center>There are currently no products in this section</center></b>";
else {
  $x = $results_per_page * ($page_numer - 1);
  for($i=$x;$i<$results_per_page;$i++) {
    if($prods[$i]['imageurl'] == null) echo "<a href='{$prods[$i]['url']}' target='_blank' rel='nofollow'><img src='assets/images/blank.gif' 
align='right' border='0' width='100' height='100' alt='{$prods[$i]['name']}'/></a><b><a href='{$prods[$i]['url']}' target='_blank' 
rel='nofollow'>{$prods[$i]['name']}</a></b><br/>{$prods[$i]['description']} <a href='{$prods[$i]['url']}' target='_blank' rel='nofollow'>Read 
more</a><br/><br/><b>Price:</b> £{$prods[$i]['price']}<br clear='all'><br/><br/>";
    else echo "<a href='{$prods[$i]['url']}' target='_blank' rel='nofollow'><img src='{$prods[$i]['imageurl']}' align='right' border='0' width='125' 
height='123' alt='{$prods[$i]['name']}'/></a><b><a href='{$prods[$i]['url']}' target='_blank' 
rel='nofollow'>{$prods[$i]['name']}</a></b><br/>{$prods[$i]['description']} <a href='{$prods[$i]['url']}' target='_blank' rel='nofollow'>Read 
more</a><br/><br/><b>Price:</b> £{$prods[$i]['price']}<br clear='all'><br/><br/>";    
  }    
}
echo "Page: ";

$number_of_pages = ceil($num / $results_per_page);
for($i=1;$i<$number_of_pages;$i++) {
  if($i == $page_numer) echo " $i | ";
  else echo "<a href='?p=$i'>$i</a> |";
}
echo "<br><br>"; 

-Ben

[markl999]

Try changing:
for($i=$x;$i<$results_per_page;$i++) {

to:
for($i=0;$i<$results_per_page;$i++) {

[zink]

Excellent Thanks Mark! Works like a dream.

The only other thing is the price is displaying as "0.00", I think it has something to do with the array and a string format instead of float.

Thanks again!
Ben

[markl999]

Maybe it's because you have:
$return[$i]['price'] = number_format($return['price'], 2);
instead of:
$return[$i]['price'] = number_format($return[$i]['price'], 2);

[zink]

You're right !

OMG even I should have spotted that one!

Thanks for everything Mark.

-Zink