Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
Thread: Do math operation only once
01-23-2013, 03:20 AM #1
- Join Date
- May 2009
- Thanked 12 Times in 12 Posts
Do math operation only once
I have a query that looks something like this:
SELECT ((`column_1` / `column_2`) * `column_3`) as `math_result` FROM `table` WHERE `criteria` > '27'
SELECT ((`column_1` / `column_2`) * `column_3`) as `math_result` FROM `table` WHERE `criteria` > `math_result`
SELECT ((`column_1` / `column_2`) * `column_3`) as `math_result` FROM `table` WHERE `criteria` > ((`column_1` / `column_2`) * `column_3`)
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`
Last edited by TheBlueblib; 01-23-2013 at 03:37 AM.
01-23-2013, 08:00 PM #2
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
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).Code:SELECT ... FROM table WHERE field LIKE '%something%'
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.
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.