Search code examples
databasealgorithmsearchsearch-suggestionjaro-winkler

Search Recommandation / Suggestion on large database

I have table with millions of rows. Now when User make any spelling mistake while searching string or word from table, I want to recommend user correct word or string from table. I am using jaro-winkler algorithm to compare distance of string, but as my database is huge my query is taking so much time in execution. Some how I will have to minimize data before applying jaro-winkler algorithm.

Is there any other better way through which I can get quick search recommendation from large table.

I want to build similar search like google search without auto suggestion. I am using my sql database.

Please suggest.

Solution

  • You probably want to try a SOUNDEX https://en.wikipedia.org/wiki/Soundex query. https://msdn.microsoft.com/en-us/library/ms187384.aspx

    • ALTER the table and add a column for soundex of the word
    • UPDATE the table to fill in the soundex for the word
    • Add an INDEX on the soundex column
    • SELECT * FROM Table WHERE WordSoundex = SOUNDEX(@query)