Jump to content


Photo

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


  • Please log in to reply
2 replies to this topic

#1 bcamp1973

bcamp1973
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts

Posted 23 August 2006 - 09:49 PM

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?

#2 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 24 August 2006 - 12:41 PM

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:
SELECT * FROM WHERE MATCH(col) AGAINST('"string*"' IN BOOLEAN MODE);
http://dev.mysql.com...xt-boolean.html

To match misspellings you could use (if available) the pspell 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 soundex 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.

#3 bcamp1973

bcamp1973
  • Members
  • PipPipPip
  • Advanced Member
  • 133 posts

Posted 25 August 2006 - 03:32 AM

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 :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users