Re: Reduce Calculations in SELECT

On Tue, Aug 31, 2010 at 10:16 AM, Carel Combrink <s25291930(at)tuks(dot)co(dot)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