SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast zink's Avatar
    Join Date
    Jul 2006
    Location
    UK
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Slow mySQL query

    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:

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

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

  2. #2
    Sell crazy someplace else markl999's Avatar
    Join Date
    Aug 2003
    Location
    Manchester, UK
    Posts
    4,007
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Enthusiast zink's Avatar
    Join Date
    Jul 2006
    Location
    UK
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

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

  4. #4
    Sell crazy someplace else markl999's Avatar
    Join Date
    Aug 2003
    Location
    Manchester, UK
    Posts
    4,007
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You need to LIMIT the query, for example,
    PHP Code:
    $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:
    PHP Code:
    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);

  5. #5
    SitePoint Enthusiast zink's Avatar
    Join Date
    Jul 2006
    Location
    UK
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:

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

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

  6. #6
    Sell crazy someplace else markl999's Avatar
    Join Date
    Aug 2003
    Location
    Manchester, UK
    Posts
    4,007
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try changing:
    for($i=$x;$i<$results_per_page;$i++) {

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

  7. #7
    SitePoint Enthusiast zink's Avatar
    Join Date
    Jul 2006
    Location
    UK
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  8. #8
    Sell crazy someplace else markl999's Avatar
    Join Date
    Aug 2003
    Location
    Manchester, UK
    Posts
    4,007
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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);

  9. #9
    SitePoint Enthusiast zink's Avatar
    Join Date
    Jul 2006
    Location
    UK
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You're right !

    OMG even I should have spotted that one!

    Thanks for everything Mark.

    -Zink


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
  •