CODEWITHSUNDEEP
mysqlsqlselect
Danny
Danny

Reputation: 183

How to avoid repeating expressions in SELECT?

I am using a MySQL SELECT query in a way that it uses stored function to do some computation (that involves running some queries too). But I want to sort by the value that is returned by the function.

Something like this:

SELECT func_call(t1.id1,t2.id2,SUM(t3.id3)) FROM table1 t1
JOINS ----
WHERE ---
GROUP BY (t2.id2)  
ORDER BY func_call(t1.id1,t2.id2,SUM(t3.id3))
DESC LIMIT 10

Now is the internal function cache maintained? Can I store the result somehow in a variable and use it? I just want to decrease the execution time.

Upvotes: 1

Views: 268

Answers (1)

Mureinik
Mureinik

Reputation: 311338

Any decent optimizer would use the same result for the select list and the order by clause, without calling it twice. You could, however, make the query less cumbersome by specifying the order by list with ordinal arguments instead of columns:

SELECT   func_call(t1.id1,t2.id2,SUM(t3.id3))
FROM     table1 t1
JOIN     ----
WHERE    ---
GROUP BY t2.id2
ORDER BY 1 DESC -- Here
LIMIT    10

Upvotes: 1

Related Questions