Hi all,
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
