Re: [OT] MySql Soundex

["David Buckley" <db@xxxxxxxxxxx>]

--- In ukha_d@xxxxxxx, Doogie Brodie <ukhad@d...> wrote:

> AIUI, a soundex is calculated on a single word.

Certainly is; there a description of how to do soundex in Knuth, if
my memory serves, and everyone seems to do it in much the same way,
getting keys in a format like S102

> You would have to split your phrases and multiple words
> into seperate words before querying for their soundex. You
> should be able to get away with searching for a space
> delimiter and building an array or #table, or
> whatever your programming language allows, and then
> cycling through that comparing the soundexes.

On a recent big (oracle) job in which I was involved, this is
exactly what the SQL boys did; triggers were used to keep separate
part-name tables up to date, and one of the part-name tables was a
soundex.  Soundexes are most widely used with names.  By having
separate part-name tables it makes it realistically possible to
find "lee yan" as well as "yan lee", which may sound
silly, but in
some cultures name order is less significant than in others.

Should you fancy trying this with oracle and millions of names, be
advised that query hinting (no matter how ugly you consider this
practice to be) makes the difference between query return times of a
second or so and twenty minutes....

Also, and I believe this applies more widely than Oracle; dont put a
function (any function) directly into a query, as Oracle then always
does a full table scan.  Convert the thing using a function first,
and then use that converted value in the query.  The corollary of
this is that you store function converted stuff in the table.
Tradeoff is extra colums (or possibly tables) versus query execution
time.