Optimizing a search query

Databases
1

I’ve been using a php search class for years - it works really well and uses the levenshtein function to dynamically pull extremely relevant results for any number of DB columns. The problem is is pulls every single row in the database and runs an algorithm on them. This is actually faster than it sounds ( about 0.3 seconds on 30,000 rows), but I was hoping to optimize it by doing it entirely in sql.

Here is a query that runs extremely fast (about 0.02 seconds on 30,000 rows):

SELECT id, firstName, ((CHAR_LENGTH(firstName) - levenshtein('robert', firstName)) / CHAR_LENGTH(firstName))  AS matchPercentage
FROM Clients

The problem is now I need to order those results by the calculated field ‘matchPercentage’, and as soon as I do that it bogs down to around 18 seconds, i.e.

SELECT id, firstName, ((CHAR_LENGTH(firstName) - levenshtein('robert', firstName)) / CHAR_LENGTH(firstName))  AS matchPercentage
FROM Clients
GROUP BY id
ORDER BY matchPercentage DESC

I’ve also tried creating temporary tables, etc but everything seems to run around 18 seconds.

Is it possible to achieve what I’m wanting efficiently?

2

I am re-reading my post, and to clarify: my php Search class does not pull every row in the database, it pulls every row in a given table. Also, the levenshtein function in the SQL above is a stored procedure.

So none of this is mysql specific really - it’s more of a question of “how do I order results by a DBMS-intensive algorithm?”

3

Guess this is impossible, huh?
Oh well I can live with the current setup.