I am trying to do a fulltext search on my database of 3.3 million rows (~ 1 Gb). I
attempted to make the searching faster by concatenating all columns I wanted to search
and placed them in a table:
product_fulltext (product_id, search_text)
Then I added the fulltext index:
ALTER TABLE product_fulltext ADD FULLTEXT search_ft (search_text)
The index was created in about 9 minutes.
Next, I need the results grouped into categories. For instance, if I search for
'Mountain' I would like to see the top 3 results for each category. For instance
'mountain' might return:
Apparel
----------------
Mountainsmith Backpack
Mountain Hardware Sub Zero Jacket
Mountain Horse Stable Loafer
Food & Beverage
--------------------
Green Mountain Coffee
Mountain Grown Fodgers
Mountain Berry Tart
Video Games
--------------------
No Fear Downhill Mountain Biking
Mountain Climber - Xbox
Cliff Hanger
Books
--------------------
Mountain High, Mountain Rescue
Mountain Bike Magic
Go Tell It on the Mountain
.. etc ...
Obviously doing a fulltext search on each category individually wouldn't be fast since
there are about 20 categories. I decided instead to make a TEMPORARY TABLE, insert
all the matching product_ids into it, then group by category and limit for the top 3.
Unfortunately, the temporary table insert combined with the fulltext query takes much
too long:
CREATE TEMPORARY TABLE temp_table_326 SELECT product_id FROM product_fulltext WHERE
MATCH ( search_text ) AGAINST ('Mountain')
Query took: 17.46 seconds
So I tried a plain-jane fulltext select query to see if the temp table was the issue:
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text ) AGAINST ('United
States')
Executed in: 13.52 seconds
At this point, I haven't even grouped by the top 3 results per category (not sure how
I will do that yet) or joined the products on any tables for images etc. I didn't set
any LIMIT parameters because if I said "LIMIT 20", all 20 matches could be Apparel
products and the other categories would not show up. The BEST performance I can get
is about 5 seconds on a single search term (as opposed to the 2 terms in 'United
States')
I'm at a loss here...
- J
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
