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:Microsoft Windows (Windows 2003 Server)
Assigned to: CPU Architecture:Any
Tags: full-text, fulltext, search
Triage: Needs Triage: D5 (Feature request)

[23 Nov 2006 12:08] Adam Phillips
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.
[28 Aug 2009 20:57] Sveta Smirnova
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.
[21 Sep 2009 16:13] Adam Phillips
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
[21 Sep 2009 19:42] Sveta Smirnova
Thank you for the feedback.

Feature request verified as described.