Optimizing a Soundex Query for finding similar names
- by xkingpin
My application will offer a list of suggestions for English names that "sound like" a given typed name.
The query will need to be optimized and return results as quick as possible. Which option would be most optimal for returning results quickly. (Or your own suggestion if you have one)
A. Generate the Soundex Hash and store it in the "Names" table then do something like the following: (This saves generating the soundex hash for at least every row in my db per query right?)
select name from names where NameSoundex = Soundex('Ann')
B. Use the Difference function (This must generate the soundex for every name in the table?)
select name from names where Difference(name, 'Ann') = 3
C. Simple comparison
select name from names where Soundex(name) = Soundex('Ann')
Option A seems like to me it would be the fastest to return results because it only generates the Soundex for one string then compares to an indexed column "NameSoundex"
Option B should give more results than option A because the name does not have to be an exact match of the soundex, but could be slower
Assuming my table could contain millions of rows, what would yield the best results?
