Optimizing a stored function call in SELECT and WHERE clauses

Question

I have an SQL query with the following structure:

SELECT *, storedfunc(param, table.field) as f 
FROM table 
WHERE storedfunc(param, table.field) < value 
ORDER BY storedfunc(param, table.field);

Is there a way to optimize this eliminating several function calls? Or does MySQL perform such optimization behind the scene? In fact the function is declared as deterministic.

I need also to mention that the function params are partially from selected table's columns. I changed the example slightly to reflect this.

Derringer

Is param a column in the table or a parameter? – Flageolet

Answer 1

Rewrite and test which one performs faster:

SELECT *, storedfunc(param, table.column) AS f 
FROM table 
WHERE storedfunc(param, table.column) < value 
ORDER BY f ;

SELECT *
FROM
  ( SELECT *, storedfunc(param, table.column) AS f 
    FROM table 
  ) AS tmp
WHERE f < value 
ORDER BY f ;

In MySQL, you can even write like this (warning: not standard SQL syntax):

SELECT *, storedfunc(param, table.column) AS f 
FROM table 
HAVING f < value 
ORDER BY f ;

Montelongo

Strangely, I tried the later but MySQL showed an error. Will try again. – Derringer

Can you post exactly what you tried? And what the error message was? – Flageolet

In fact, I tried f in where clause, not in having, this is why it produced the error. Anyway, does MySQL peerform the optimization automatically, due to deterministic function declaration? – Derringer

Not sure, have to test that. And it probably depends on the version of MySQL. Newer versions have better chances to be performing the optimization. – Flageolet