Bug #14521 FULLTEXT index provides no way to access indexed word lists
Submitted: 31 Oct 2005 20:31 Modified: 28 Aug 2009 21:07
Reporter: Ian Gulliver Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server Severity:S4 (Feature request)
Version:5.0.15 OS:Linux (Debian Sid)
Assigned to: CPU Architecture:Any

[31 Oct 2005 20:31] Ian Gulliver
Description:
It would be useful to have a method to analyze the working of the FULLTEXT match generically and for a given index.  The end result is to produce something akin to Google's search explanation:

Results 1 - 10 of about 2,080 for May "I have" your headfones. (0.54 seconds) 
Did you mean: May "I have" your headphones

I would suggest something like an ANALYZE_FULLTEXT function:

> SELECT ANALYZE_FULLTEXT('May "I have" your headfones Mo Bbo common', db_name,index_name);
+-----------+----------------+-----------------+---------------------+
| Keyword   | Discard_reason | Typo_suggestion | Phonetic_suggestion |
+-----------+----------------+-----------------+---------------------+
| May       | Stopword       | NULL            | NULL                |
| I have    | NULL           | NULL            | NULL                |
| your      | Stopword       | NULL            | NULL                |
| headfones | Not indexed    | NULL            | headphones          |
| Mo        | Too short      | NULL            | NULL                |
| Bbo       | Not indexed    | Bob             | NULL                |
| common    | Over 50% match | NULL            | NULL                |
+-----------+----------------+-----------------+---------------------+

The typo suggestions could be derived from standard typo rules (one character extra/missing, two characters switched).  The phonetic suggestions could use soundex (in this case, 'headfones' and 'headphones' both evaluate to H3152).  The suggestions could appear for discards (using the matching value that appears most often in the data), or for other keywords when the suggestion word appears more often than the keyword.

How to repeat:
Feature request.
[28 Aug 2009 21:07] Sveta Smirnova
Thank you for the reasonable feature request.