MySQL: Optimising a query involving calculations

How can I optimise a query that uses the same calculation for WHERE and ORDER BY?

For example:

SELECT a, b
FROM my_table
WHERE    SIN(a) + COS(b) + TAN(5.5) < 10
ORDER BY SIN(a) +开发者_运维百科 COS(b) + TAN(5.5)

5.5 and 10 in this example are user-supplied values that will change between queries. This is a simplification -- the actual calculation is relatively complex, so it is not possible to store the result in another column.

Due to an application limitation, I cannot add the calculation to the SELECT statement. How do I prevent MySQL from having to perform the same calculation twice per row?

Finally, should I be using WHERE or HAVING to filter by the result of a calculation?

Many thanks.

I don't have my development machine in front of me, does this work?

SELECT a, b
FROM my_table
ORDER BY ( SIN(a) + COS(b) + TAN(5.5) )
HAVING   ( SIN(a) + COS(b) + TAN(5.5) ) < 10

If it does the HAVING statement shouldn't be recalculating the equation.

You could do this by storing a calculated column, which is updated via a trigger. The downsides are that it would take up some extra space, and it would slow down inserts and updates. However, depending on your specific situation, I may even be faster. It also depends on how many calculations you man need. Do you need cos,sin,tan values for all of a,b,c for various other things in your database, or do you just need sin(a), cos(b), and tan(c). Maybe you can even get away with storing the final result of SIN(a) + COS(b) + TAN(c).

try this

SELECT a, b from (
   SELECT a, b, SIN(a) + COS(b) + TAN(5.5) as filter
   FROM my_table
) as tmp1
WHERE  filter < 10
ORDER BY filter