using MATCH AGAINST - can you build for misspellings/partial matches?

[bcamp1973]

i'm trying to build a modest search engine for a site i'm working on.  part of it is to really make life easier for users and the other is just to learn :)  on that note, i'd like to build the engine to handle partial matches.  So if a user enters "Olive" they'll get a match on "Olives".  Also, if a user misspells words i'd like to build in some intelligence for that.  However, i'm not even sure where to start.  I'm using FULL TEXT indexing on the fields i'm searching against and using MATCH AGAINST to make the query. I did it that way (as opposed to useing LIKE) for performance reasons.  Maybe it was a bad choice?  Thoughts anyone?

[shoz]

I don't think you can match misspellings in a FULLTEXT search using only MYSQL. You can however do partial matches in the FULLTEXT search using BOOLEAN MODE.

eg:
[code]
SELECT * FROM WHERE MATCH(col) AGAINST('"string*"' IN BOOLEAN MODE);
[/code]
http://dev.mysql.com/doc/refman/4.1/en/fulltext-boolean.html

To match misspellings you could use (if available) the [url=http://www.php.net/pspell]pspell[/url] functions to check and find correct spellings of possibly misspelled words. You could then replace the words in the search with the correctly spelled ones. Although, it may be better to give the user the option of putting the correctly spelled word in their search as a word may only seem to be misspelled when it's not. Similar to what google does.

If you can't use pspell functions, then you could possibly make your own function to find misspelled words using the [url=http://www.php.net/soundex]soundex[/url] function. You'd create a custom dictionary with words and their soundkey and if the word entered doesn't exist in the standard dictionary you'd test against the soundkey dictionary to find possible mispellings. You'll have to test how effective this is.

MYSQL also has a SOUNDEX function but I don't see how it would help in this situation.

[bcamp1973]

ufda...so, i either need to get a hosting provider with pspell, get my own box and custom build PHP or build my own custom spelling function.  hmm.  i may wait then and go with the custom box later if traffic warrants it.  i really want to provide this for users, but not at the expense of other priorities at the moment. thanks for the great feedback tho! much appreciated :)