How do I efficiently make use of the result of a function call in a MySQL query multiple times without calling the function multiple times?

Question

I have an SQL query like:

SELECT blah
  FROM table
 WHERE field1 % functCall(otherField1, otherField2) = 0
    OR field2 % functCall(otherField1, otherField2) = 0
    OR field3 % functCall(otherField1, otherField2) = 0

Is there a way that I can only call functCall once, reusing it's result in the other two comparisons?

Thanks!

Answer 1

MySQL will automatically optimize your query so that the function is only called once and the result will be reused.

If you want to avoid the repeated code you can evaluate the function in a derived table and then query that.

SELECT blah
FROM
(
    SELECT 
        blah, field1, field2, field3,
        functCall(otherField1, otherField2) AS f
    FROM your_table
) T1
WHERE field1 % f = 0
   OR field2 % f = 0
   OR field3 % f = 0