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: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com > -----Original Message----- > From: Mike Morton [mailto:[EMAIL PROTECTED] > Sent: Monday, October 29, 2007 5:39 PM > To: mysql@lists.mysql.com > 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: 905-465-1263 > * Email: [EMAIL PROTECTED] > * > **************************************************** > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
