CODEWITHSUNDEEP
mysqlfunctioncallreusabilityprocessing-efficiency
John Kurlak
John Kurlak

Reputation: 6690

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?

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!

Upvotes: 3

Views: 94

Answers (3)

nate c
nate c

Reputation: 9005

Use a sub-query in the from clause, then check the condition in the outer query:

 SELECT blah
 FROM
   (select functCall(f1, f2) as fc, f1, f2, f3 from table) as t 
 WHERE f1 % fc = 0
    OR f2 % fc = 0
    OR f3 % fc = 0

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 839234

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

Upvotes: 2

Orson
Orson

Reputation: 15451

Store the result of the function in a variable first the use it in your query.

Upvotes: 0

Related Questions