MYSQL: how to find entries corresponding to MIN() of costly function

I am running a complicated and costly query to find the MIN() values of a function grouped by another attribute. But I don't just need the value, I need the entry that produces it + the value.

My current pseudoquery goes something like this:

SELECT MIN(COSTLY_FUNCTION(a.att1,a.att2,$v1,$v2)) FROM (prefiltering) as a GROUP BY a.group_att;

but I want a.* and MIN(COSTLY_FUNCTION(a.att1,a.att2,$v1,$v2)) as my result.

The only way I can think of is using this ugly beast:

SELECT a1.*, COSTLY_FUNCTION(a1.att1,a1.att2,$v1,$v2)
FROM (prefiltering) as a1 
WHERE COSTLY_FUNCTION(a1.att1,a1.att2,$v1,$v2) = 
  (SELECT MIN(COSTLY_FUNCTION(a.att1,a.att2,$v1,$v2)) FROM (prefiltering) as a GROUP BY a.group_att) 

But now I am executing the prefiltering_query 2 times and have to run the costly function twice. This is ridiculous and I hope that I am doing something seriously wrong here.

Possible solution?:

Just now I realize that I could create a temporary table conta开发者_JAVA技巧ining:

(SELECT a1.*, COSTLY_FUNCTION(a1.att1,a1.att2,$v1,$v2) as complex FROM (prefiltering) as a1)

and then run the MIN() as subquery and compare it at greatly reduced cost. Is that the way to go?

A problem with your temporary table solution is that I can't see any way to avoid using it twice in the same query.

However, if you're willing to use an actual permanent table (perhaps with ENGINE = MEMORY), it should work. You can also move the subquery into the FROM clause, where it might be more efficient:

CREATE TABLE temptable ENGINE = MEMORY
  SELECT a1.*,
    COSTLY_FUNCTION(a1.att1,a1.att2,$v1,$v2) AS complex
  FROM prefiltering AS a1;

CREATE INDEX group_att_complex USING BTREE
  ON temptable (group_att, complex);

SELECT a2.*
FROM temptable AS a2
  NATURAL JOIN (
    SELECT group_att, MIN(complex) AS complex
    FROM temptable GROUP BY group_att
  ) AS a3;

DROP TABLE temptable;

(You can try it without the index too, but I suspect it'll be faster with it.)

Edit: Of course, if one temporary table won't do, you could always use two:

CREATE TEMPORARY TABLE temp1
  SELECT *, COSTLY_FUNCTION(att1,att2,$v1,$v2) AS complex
  FROM prefiltering;

CREATE INDEX group_att_complex ON temp1 (group_att, complex);

CREATE TEMPORARY TABLE temp2
  SELECT group_att, MIN(complex) AS complex
  FROM temp1 GROUP BY group_att;

SELECT temp1.* FROM temp1 NATURAL JOIN temp2;

(Again, you may want to try it with or without the index; when I ran EXPLAIN on it, MySQL didn't seem to want to use the index for the final query at all, although that might be just because my test data set was so small. Anyway, here's a link to SQLize if you want to play with it; I used CONCAT() to stand in for your expensive function.)

You can use the HAVING clause to get columns in addition to that MIN value. For example:

SELECT a.*, COSTLY_FUNCTION(a.att1,a.att2,$v1,$v2) FROM (prefiltering) as a GROUP BY a.group_att HAVING MIN(COSTLY_FUNCTION(a.att1,a.att2,$v1,$v2)) = COSTLY_FUNCTION(a.att1,a.att2,$v1,$v2);