Mysql – how to make levenshtein distance faster for thesql

functionsMySQL

I am trying to make a similar query faster:

SELECT levenshtein(substring(keywords, 1,5),'texas') as distance   
FROM stuff ORDER BY distance ASC

Unfortunately and as expected, the query takes an eternity. How can I accomplish the same thing, but make it fast? The table is 5 million rows.

Best Answer

Functions are slow and it is a complex function. Try optimizing the function.

Or select down to like .NET and do it there.

If you get a lot of exact matches you can make that separate. 

SELECT o as distance   
FROM stuff ORDER BY 
where substring(keywords, 1,5) = 'texas')

union 

SELECT levenshtein(substring(keywords, 1,5),'texas') 
from FROM stuff
where substring(keywords, 1,5) <> 'texas')
FROM stuff ORDER BY distance ASC
Related Question