List:General Discussion« Previous MessageNext Message »
From:Rick James Date:June 3 2013 9:43pm
Subject:RE: string-likeness
View as plain text  
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:
> To unsubscribe:

string-likenesshsv3 Jun
  • Re: string-likenessJohan De Meersman3 Jun
  • Re: string-likenessHartmut Holzgraefe3 Jun
    • Re: string-likenesshsv4 Jun
  • RE: string-likenessRick James3 Jun
    • Re: string-likenessMichael Dykman3 Jun
    • RE: string-likenesshsv6 Jun