I will second Rick's approach and have implemented something very
similar for a client when soundex feel short of expectation. It
worked very well.
On Mon, Jun 3, 2013 at 5:43 PM, Rick James <rjames@stripped> wrote:
> Soundex is the 'right' approach, but it needs improvement. So, find an improvement,
> then do something like this...
> Store the Soundex value in a column of its own, INDEX that column, and JOIN on that
> column using "=". Thus, ...
> * You have spent the effort to convert to Soundex once, not on every call.
> * Multiple strings will have the same Soundex, but generally not many will have the
> same. Hence, the JOIN won't be 1:1, but rather some small number.
> Other approaches (eg, Levenshtein) need both strings in the computation. It _may_ be
> possible to work around that by the following.
> Let's say you wanted to a "match" if
> * one letter was dropped or added or changed, or
> * one pair of adjacent letters was swapped.
> Then... For a N-letter word, store N+1 rows:
> * The word, as is,
> * The N words, each shortened by one letter.
> Then an equal match on that hacked column will catch single dropped/added/changed
> letter with only N+1 matches.
> (Minor note: doubled letters make the count less than N+1.)
>> -----Original Message-----
>> From: hsv@stripped [mailto:hsv@stripped]
>> Sent: Monday, June 03, 2013 8:30 AM
>> To: mysql@stripped
>> Subject: string-likeness
>> I wish to join two tables on likeness, not equality, of character strings.
>> Soundex does not work. I am using the Levenstein edit distance, written in
>> SQL, a very costly test, and I am in no position to write it in C and link
>> it to MySQL--and joining on equality takes a fraction of a second, and
>> this takes hours. Any good ideas?
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql
- michael dykman
May the Source be with you.