So far as I know, the only way to accomplish what you want is to do what you tried: put the Boolean match in the WHERE clause and order by a natural match. That's what I do on our web site.
By the way, you can't use prefix stemming: "*abcd" is the same as "abcd". 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: Tanner Postert [mailto:[EMAIL PROTECTED] > Sent: Thursday, July 26, 2007 7:56 PM > To: mysql@lists.mysql.com > Subject: fulltext search option > > I'm having a problem with the fulltext searching, and was > looking for some > help. > > i'm currently working with the following query: > > select table.* > from table where match(title, description) against ('*search term*' IN > BOOLEAN MODE) > > the reason I am using boolean mode, is so that it matches things like > "search terms", rather than excluding those results if i left off the > boolean mode. > > my problem, is that the exact phrase "search term" shows up > in the result > set, but is not ranked the highest since it's only a boolean > search, its > just checking if it exits. > > if I add: match(title,description) against ('search term') as score > to my select and order by score, it works fine, but my query > time goes from > 0.0623 to 3.7 seconds. which seems like a huge give in > performance, which I > assume comes from ordering by a dynamically created field, so > I tried to > fake out the query by changing the query to > select table.* from table where (match(title, description) > against ('search > term') or match(title, description) against ('*search term*' > in boolean > mode)) > but the ordering is still off. > > is there a way to have ordered by the non-boolean query, but > still be able > to include those results? > > seems bad that I can only have exact matches and proper > ordering, or partial > matches and no ordering. > > > > > > select video.id, video.title, video.description, > (match(title, description) against ('woot monkey')) as straight, > (match(title, description) against ('*woot monkey*' in > boolean mode)) as > stars, > (match(title,description) against ('"woot monkey"' in boolean > mode)) as > exact > from video where (match(title, description) against ('*woot > monkey*' IN > BOOLEAN MODE) > and file_complete = 'true') order by exact DESC, stars DESC, > straight DESC > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
