MySQL Bugs: #24540: Fulltext Searching

Bug #24540	Fulltext Searching
Submitted:	23 Nov 2006 12:08	Modified:	21 Sep 2009 19:42
Reporter:	Adam Phillips	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S4 (Feature request)
Version:	5	OS:	Windows (Windows 2003 Server)
Assigned to:		CPU Architecture:	Any
Tags:	full-text, fulltext, search

Description:
A great enhancement to the power and flexibility of the fulltext searching would be to enable the weighting of individual words. Hence:

For example:

-> SELECT * FROM articles
-> WHERE MATCH (title,body) AGAINST (WORD_1_WEIGHT * 'database' WORD_2_WEIGHT * 'mysql');

i.e. To be able to increase or decrease the relevancy of a word or phrase in the search term.

How to repeat:
A great enhancement to the power and flexibility of the fulltext searching would be to enable the weighting of individual words. Hence:

For example:

-> SELECT * FROM articles
-> WHERE MATCH (title,body) AGAINST (WORD_1_WEIGHT * 'database' WORD_2_WEIGHT * 'mysql');

i.e. To be able to increase or decrease the relevancy of a word or phrase in the search term.

Thank you for the report.

But there are "<" and ">" operators for boolean search already. See at http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html:

# > <

These two operators are used to change a word's contribution to the relevance value that is assigned to a row. The > operator increases the contribution and the < operator decreases it. See the example following this list.

Please inform us if this is what you want or explain which additional functionality do you need.

I understand that there are "<" and ">" operators for boolean search already, but I was hoping for something more along the lines of a percentage qualifier for deeming how relevant the terms are: for example

STANDARD FULLTEXT MATCHING: match (fulltext_column) against ('term1 term2 term3')

PROPOSED: match (fulltext_column) against ('term1[.2] term2[.5] term3[.9]')

meaning that term1 is at 20%, term2 at 50% and term3 at 90% relevancy.

SQL Server (sorry) already offers this functionality and I would love to see it in MySQL.

Thank,
Adam

Thank you for the feedback.

Feature request verified as described.