mysql - Calculated column in where-clause - performance

Question

Since you can't use a calculated column in a where-clause in MySQL like this:

SELECT a,b,c,(a*b+c) AS d FROM table
WHERE d > n
ORDER by d

you have to use

SELECT a,b,c,(a*b+c) AS d FROM table
WHERE (a*b+c) > n
ORDER by d

Is the calculation (in that example "(a*b+c)" executed once per row or twice? Is there a way to make it faster? I find it strange it's possible to ORDER on the column but not to have a WHERE-clause.

Answer 1

You can use HAVING to filter on a computed column:

SELECT a,b,c,(a*b+c) AS d, n FROM table
HAVING d > n
ORDER by d

Note that you need to include n in the SELECT clause for this to work.