I am working on a search result query in which the user types a keyword and the top 3 results for each category are displayed with 24 or so total results on the page. The database is large (8 GB) and the table in which the fulltext search occurs has 3 million rows.
Tables:
product
category
category_link
The category table contains many categories but I want to output only top level "general" categories. Each product has a link to its general category in the category_link table.
The fulltext search runs fine if I don't attempt to order and group the output by categories. Currently I have 1 query that gets the top level categories (about 20 in all) then it loops through in PHP executing a fulltext search on EACH of those results. This probably isn't the best solution since it requires executing a fulltext search 20 different times and in some cases (especially general words or multiword searches) takes much too long. (anywhere from 2 - 45 seconds)
Is there a way to combine this into 1 FAST query? I'm using MySQL 4.0.17 and PHP 4.3.4 so I don't think subqueries are an option... I suppose I could upgrade if it was totally necessary.
The desired results for searching for "Caffeine" would look like this: -------------------------------------------------------------- Search results for "Caffeine"
>Drinks - Caffeine Free Coke - Caffeine Free Pepsi - Jolt
>Health Products - NoDoz - Generic Caffeine Tablets - Caffeine Free Sleepytime Tea
>Books - Conquering Caffeine Dependence - Understanding Caffeine : A Biobehavioral Analysis - Over-the-Counter Drug Index 2004
>Music -DJ Micro - Caffeine: The Natural Stimulant -Magnet - Caffeine Superstar ----------------------------------------------------------------
After each category I have a link to "See all Matches in Category XYZ" but that query runs fine since the dataset to search is reduced in size to just that particular category.
The simplified pseudo-code of what I am currently using is this:
GET TOP LEVEL CATEGORIES AND PLACE IN ARRAYS ( I left this code out)
// the resulting arrays might look like this:
$category_id = array("1","2","3");
$category_name = array("One","Two","Three");
for($i=0;$i<count($category_id);$i++){
$query = "SELECT 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] . "'
LIMIT 0, 3 ";
$result = mysql_query($query);
print(....) // output the 3 results
print("See all matches in " . $category_name[$i]);
}
Thanks for any help!!! - John
