Re: Fulltext Relevancy not returning anticipated results?

[Mike Morton]

Jerry:

Sorry - I should have mentioned in my previous email that we have changed
that min. word length to 2 in the config already, as we have MANY 3 letter
searches... ;)

Any other suggestions?


On 10/30/07 9:12 AM, "Jerry Schwartz" <jschwartz@stripped> wrote:

> By default, MySQL ignores any "word" with less than four characters when
> doing full text searches. You can change this in my.conf, the setting should
> be obvious.
> 
> I had to do this so that customers could find inventory items that were red.
> 
> Regards,
> 
> Jerry Schwartz
> The Infoshop by Global Information Incorporated
> 195 Farmington Ave.
> Farmington, CT 06032
> 
> 860.674.8796 / FAX: [phone]
> 
> www.the-infoshop.com
> www.giiexpress.com
> www.etudes-marche.com
> 
>> -----Original Message-----
>> From: Mike Morton [mailto:mike@stripped]
>> Sent: Monday, October 29, 2007 5:39 PM
>> To: mysql@stripped
>> Subject: Fulltext Relevancy not returning anticipated results?
>> 
>> I have a database of products, doing a search on them trying to achieve
>> a
>> modicum of relevancy, but am getting a strange result on some returned
>> rows:
>> 
>> QUERY:
>> select *,match(code) against ('vic*' IN BOOLEAN MODE) * 8 + match(name)
>> against ('vic*' IN BOOLEAN MODE) * 4 + match(small_desc) against
>> ('vic*' IN
>> BOOLEAN MODE) * 2 + match(large_desc) against ('vic*' IN BOOLEAN MODE)
>> as
>> score from products where active='y' and site like '%,1,%' and
>> match(code,name,small_desc,large_desc) against ('vic*' IN BOOLEAN MODE)
>> order by score desc
>> 
>> I get returned 6 rows from the product database.  The strange thing is,
>> there is a product (at least one that is known about), with the name:
>> "Salchichon de Vic" that is NOT being returned as a result.
>> 
>> When I break out the scores, I get a match on the first three products
>> returned for the titles:
>> 
>> 6-Pack Vichy Catalan Sparkling Spring Water
>> 2-Pack Vichy Catalan Mineral Water
>> San Vicente - Tempranillo 2001
>> 
>> And the other three products have a match in the large_desc.
>> 
>> What is confusing me is why I am not getting any match on the titles
>> for the
>> "Salchichon de Vic" - which by my thought process should be the most
>> relevant of all returns?
>> 
>> Any thoughts on this?  The term "vic" used in this case is the search
>> string
>> submitted by the user.
>> 
>> You can see the search in action at
>> http://www.tienda.com/support/search.html
>> 
>> --
>> Cheers
>> 
>> Mike Morton
>> 
>> ****************************************************
>> *
>> * Tel: [phone]
>> * Email: mike@stripped
>> *
>> ****************************************************
>> 
>> 
>> 
>> 
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>> infoshop.com
> 
> 
> 
> 

-- 
Cheers

Mike Morton

****************************************************
*
* Tel: [phone]
* Email: mike@stripped
*
****************************************************