MySQL: Selecting the rows having min value of a computed column

Question

The naive way of doing this that comes to mind would be:

SELECT name, lev FROM
(SELECT name, levenshtein(name, *parameter*) as lev FROM my_table)
WHERE 
lev = (SELECT MIN(lev) FROM 
(SELECT name, levenshtein(name, *parameter*) as lev FROM my_table ));

However the "(SELECT name, levenshtein(name, parameter) as lev FROM my_table)" subquery, which is very expensive (huge table) is repeated twice which seems horribly inefficient.

I somehow though you could write :

SELECT name, lev FROM
(SELECT name, levenshtein(name, *parameter*) as lev FROM my_table) as my_temp_table
WHERE 
lev = (SELECT MIN(lev) FROM my_temp_table);

But it doesn't seem to work.

Is there a clean way to optimize that query for speed? Did I miss something obvious?

Do I have to rely on temporary tables? (trying to avoid it due to the overhead/complexity as they don't seem appropriate for very frequent/concurrent queries)

Any input from SQL ninjas would be greatly appreciated ;)

Answer 1

select * from
(
SELECT * 
FROM `test`.`test`
)
as temp
where compute_total_price_single=(select min(compute_total_price_single))
;

This is what I did for my problem, since it worked I suspect the following would also work:

SELECT name, lev FROM
    (SELECT name, levenshtein(name, *parameter*) as lev FROM my_table) as my_temp_table
WHERE 
lev = (SELECT MIN(lev));

I'm using MySQL 5.

Answer 2

SELECT * FROM
(
    SELECT * 
    FROM `test`.`test`
) as temp
WHERE compute_total_price_single = (SELECT MIN(compute_total_price_single));

Answer 3

SELECT name, min(levenshtein(name, *parameter)) as lev
FROM my_table
GROUP BY name;