Do math operation only once

[TheBlueblib]

I have a query that looks something like this:

SELECT ((`column_1` / `column_2`) * `column_3`) as `math_result` FROM `table` WHERE `criteria` > '27'

What I want to accomplish is to have the where clause select rows where 'criteria' is greater than 'math_result', so this is what I would have hoped would work but obviously I get a syntax error "unknown column 'math result'":

SELECT ((`column_1` / `column_2`) * `column_3`) as `math_result` FROM `table` WHERE `criteria` > `math_result`

What I can do is the math operation twice, so this is a current solution, albeit inefficient:

SELECT ((`column_1` / `column_2`) * `column_3`) as `math_result` FROM `table` WHERE `criteria` > ((`column_1` / `column_2`) * `column_3`)

Assuming MySQL doesn't cache the calculation of 'math_result' and it is inefficient, what is the efficient solution I'm looking for? Is a subquery perhaps needed here? Having a separate column to store the 'math_result' data is a solution I've already considered and not fond of if an efficient version of the above code exists.

Much thanks.

UPDATE
So I came across the HAVING clause, and I've tried it and it seems to work in the way I want it to. Is this query a misuse of the expected usage of the HAVING clause and is there some other inefficiency I'm not seeing?

SELECT ((`column_1` / `column_2`) * `column_3`) as `math_result` FROM `table` HAVING `criteria` > `math_result`

[Old Pedant]

HAVING works *AFTER* all records have been selected, so it is much much less efficient than using WHERE, even with the re-calculation.

You should *not* assume that MySQL will actually *do* the re-calculation. The query optimizer may well realize you are using the same expression in both the SELECT and the WHERE and cache the result obtained when doing the WHERE for use by the SELECT. Certainly SQL Server will do this, though let's face it: MySQL is not in the same class as SQL Server.

But in any case, why do you think that such a simple math operation will be "expensive" in terms of performance? Almost surely, even a LIKE condition will cost much much more CPU time. That is, doing

SELECT ... FROM table WHERE field LIKE '%something%'

is probably one of *the* most expensive things you can do. Because MySQL *WILL* examine EACH AND EVERY RECORD in the entire table. It can not use an index with that kind of LIKE (it can use an index with LIKE 'xxx%' where the % is only on the end). On top of that, any kind of string comparison operation has to be done all in software, whereas your math operation can be done by the hardware (assuming, that is, that your numbers are *NOT* DECIMAL or NUMBER and are instead either INT or REAL/FLOAT).

In your own query, if all three of your columns are indexed, it *probably* can do the WHERE clause using only the indexes. (Maybe not; MySQL isn't very clever about index usage. But it would be worth a try.)

In any case, learn to use MySQL's EXPLAIN capability: This will tell you when MySQL is using indexes and when it can't.