Mysql query with user-defined-function - why function was called twice?

Question

I have a 1:1 relation between tables Entity and Contact (that corresponds to object 's inherirance). fn_match(id) is UDF which returns boolean and returns true if record matches some special criteria (additional queries inside function). That's a query:

select *
from Entity a
inner join Contact b on a.id = b.id
where fn_match(a.i)

It worked fine but join spoils performance dramatically. I have added logging and have found that fn_match was called twice for every record in Entity with fn_match(id) = true. I could fix it adding deterministic to function header, but I used to think that every function/stored procedure accessing tables' data is non-deterministic.

What is correct solution for this issue?

Answer 1

You could use a temporary table to store the results of FROM Entity WHERE fn_match(i) - but this probably wouldn't improve performance. If it spoils performance with fn_match being called twice, it seems like performance wouldn't be all that great if it was called just once for each item in Entity - so probably better to rethink the function altogether.