Hi Brent,
Oops, sorry about forgetting to post the key buffer size, its:
> key_buffer_size 262144000

As far as the "combined column" I'm using, I did it to make the fulltext as simple as 
possible and keep the index at only 1 column.  I am aware that I can create an index 
for multiple columns but basically I just selected all the columns that may contain 
keywords for each product and tossed them into 1 table with the PK as product_id.  
Just thought this might help performance... maybe I'm wrong, I don't know.

If I can ever get the MATCH, AGAINST query down to a faster speed then I will really 
focus on getting the top 3 for each category.  Sorting into categories in PHP would 
work but I'd need a much larger dataset in order to insure that I got all the possible 
matches for each category... "what if there was only 1 match in the 'Movies' category 
and it was at the bottom of the results...?"  I'm thinking that we will have to use 
some other method such as listing by relavence (fulltext ordering) then showing 
"Search within: Category X, Category Y, Category Z..." links on the side (like Ebay I 
guess).  Still the problem arises as to which categories qualify.  It would be nice to 
order them by the category with the most matches but I doubt that can be accomplished.

Previously I had not been using a LIMIT because I was going to do processing for 
category grouping etc.  However, if I do use a LIMIT, the query speeds are almost 
totally dependant on the number of rows returned:

WITH LIMIT:
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text ) AGAINST ('san 
francisco') LIMIT 1000
>Query doine in 2.01 seconds
>Num Rows: 1000

WITHOUT LIMIT:
SELECT product_id FROM product_fulltext WHERE MATCH ( search_text ) AGAINST ('san 
francisco')
>Query doine in 13.45 seconds
>Num Rows: 9287

Strange.  Is this typical or do I need to tweek my system variables?
- John

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to