SQL_CALC_FOUND_ROWS on big database

jvanv8

I am trying to pull up search results on a large database of products. Ideally I'd like to display 3 or 4 items for each category with a "See all XXX matches in category Y" after each category. I also use the number of matches within each category to output the categories by the number of matches.

That said, I decided to run a MATCH/AGAINST query on each category and use SQL_CALC_FOUND_ROWS to get the total results for each category. This allows for the category ordering as well as a message indicating to the user how many matches occured in this category.

However, SQL_CALC_FOUND_ROWS takes WAY too long due to the size of the database. The table that the MATCH/AGAINST query occurs has approx. 4 million rows (there are about 20 categories).

If the SQL_CALC_FOUND_ROWS is taken out and therefore the number of matches per catgory is unknown, the query is executed relatively fast (about 0.7 to 2.0 seconds)

Anyone have any tips,hacks,work-arounds, or alternatives???

John

mrhappiness

i'm afraid it's to late and i didn't get it right but why don't you do omething like:

$results_to_display = 3;
$sql = 'SELECT * FROM table
WHERE category = 1
ORDER BY some_column
LIMIT '.($results_to_display + 1);
$r = mysql_query($sql);
if (mysql_num_rows($r) > $results_to_display)
  echo 'There is more...';

jvanv8

I don't think that would work because you have no idea how many rows in the entire database fit the search criteria. You could do a mysql_num_rows but that will only give you 3 in your case (the limit you specified).

Here is a simplified version of my query

$browse_index = 0;
$max_display = 3;
$category_id = array("1","2","3");
$category_name = array("One","Two","Three");
for($i=0;$i<count($category_id);$i++){
   $query = "SELECT SQL_CALC_FOUND_ROWS MATCH (P.product_name) AGAINST ('" . $search_term . "') AS score, P.product_id, P.product_name,...other stuff...
   FROM product AS P, category AS C, category_link AS CL	
   INNER JOIN ... join necessary tables for images etc...
   WHERE MATCH (P.product_name) AGAINST ('" . $search_term . "') AND CL.product_id = P.product_id AND CL.category_id = '" . $category_id[$i] . "'
   ORDER BY score LIMIT $browse_index, $max_display ";
   $result = mysql_query($query);
   $tot_matches_result = mysql_query("SELECT FOUND_ROWS()");
   $tot_matches = mysql_fetch_array($tot_matches_result);
   print(....) // output all 3 results
   print("See all $tot_matches matches in " . $category_name[$i]);
}

If I take the SQL_CALC_FOUND_ROWS out of the query its fast but it doesnt calculate the total number of matches per category. Leave it in and the query can take anywhere from 8-40 seconds.