MySQL Full-text search and SOUNDEX

Question

I am trying to implement a first and last name search using full-text search and SOUNDEX (in case if the name is misspelled).

I was trying to do something like

SELECT * 
    FROM employees 
WHERE 
    MATCH SOUNDEX(first_name, last_name) AGAINST SOUNDEX('John 1969 Ivan')

but this is not a valid syntax.

What I want to achieve, is that when a user types for example "Jon Ivan", the columns

first_name | last_name
----------------------
  John        Ivan

would match.

Thank you in advance!

Allstar

Not sure whether this can be combined with MATCH...AGAINST, but maybe full text search can tackle it alone? It has some tolerance for spelling differences – Flatus

Related: Whats the easiest site search application to implement, that supports fuzzy searching? – Flatus

Answer 1

MySQL tends to fall short when it comes to text searches, fuzzy searches, misspellings, etc. I highly recommend an indexing solution like Solr. Solr supports four different types of phonetic searches: Soundex, RefinedSoundex, Metaphone, and DoubleMetaphone. It has geospatial searching and misspelling searches along with butterfly/butterflies searching. I think you will REALLY be happy with the results you get. It is lightening fast compared to MySQL

Lyn