On Tue, Aug 31, 2010 at 10:16 AM, Carel Combrink <s25291930@tuks.co.za> wrote:
> Hi,
>
> I have a SELECT statement that does the following (example only, actual
> query more complex):
> SELECT foo.id, mod(foo.one, foo.two)
> FROM my_table AS foo
> WHERE mod(foo.one, foo.two) > 2
> AND mod(foo.one, foo.two) < 6;
>
> Mod is an expensive operation and it is calculated 3 times for the same set
> of inputs. How can I get it to only calculate it once and and use the result
> in the WHERE clause and return the value of the mod?
Does rewriting as a sub-select like this help:
SELECT foo.id, foo.mymod
FROM (SELECT id, mod(one, two) AS mymod FROM mytable) AS foo
WHERE foo.mymod > 2 AND foo.mymod < 6;
Josh