优化SELECT和WHERE子句中的存储函数调用 [英] Optimizing a stored function call in SELECT and WHERE clauses

问题描述

我有一个具有以下结构的SQL查询:

I have an SQL query with the following structure:

SELECT *, storedfunc(param, table.field) as f 
FROM table 
WHERE storedfunc(param, table.field) < value 
ORDER BY storedfunc(param, table.field);

是否有一种优化方法来消除多个函数调用?还是MySQL在后台执行了这样的优化?实际上，该函数被声明为确定性的.

Is there a way to optimize this eliminating several function calls? Or does MySQL perform such optimization behind the scene? In fact the function is declared as deterministic.

我还需要提到函数参数部分来自所选表的列.我稍微修改了示例以反映这一点.

I need also to mention that the function params are partially from selected table's columns. I changed the example slightly to reflect this.

推荐答案

重写并测试哪个执行速度更快:

Rewrite and test which one performs faster:

SELECT *, storedfunc(param, table.column) AS f 
FROM table 
WHERE storedfunc(param, table.column) < value 
ORDER BY f ;

SELECT *
FROM
  ( SELECT *, storedfunc(param, table.column) AS f 
    FROM table 
  ) AS tmp
WHERE f < value 
ORDER BY f ;

在MySQL中，您甚至可以这样写(警告:不是标准的SQL 语法):

In MySQL, you can even write like this (warning: not standard SQL syntax):

SELECT *, storedfunc(param, table.column) AS f 
FROM table 
HAVING f < value 
ORDER BY f ;

这篇关于优化SELECT和WHERE子句中的存储函数调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！