sqlmysqlperformance

Calculated column in where-clause - performance

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.

Solution

  • 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.