Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    May 2009
    Posts
    75
    Thanks
    1
    Thanked 12 Times in 12 Posts

    Do math operation only once

    I have a query that looks something like this:
    Code:
    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'":
    Code:
    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:
    Code:
    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?

    Code:
    SELECT ((`column_1` / `column_2`) * `column_3`) as `math_result` FROM `table` HAVING `criteria` > `math_result`
    Last edited by TheBlueblib; 01-23-2013 at 04:37 AM.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,650
    Thanks
    80
    Thanked 4,638 Times in 4,600 Posts
    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
    Code:
    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.
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •