mysql - mysql查询是否缓存动态计算的列

Question

我有一个 mysql 查询:

SELECT my_table.* WHERE SOUNDEX(my_col)='whatever' OR SUBSTR(SOUNDEX(my_col),4)='whatever' ORDER BY SUBSTR(SOUNDEX(my_col),4)='whatever',SOUNDEX(my_col)='whatever'

substring 函数和 soundex 函数实际会被调用多少次？我的意思是对于完全相同的输入，mysql 会在一个查询的范围内缓存结果吗？

如果不是，我如何更改查询以使每个函数的调用次数尽可能少。

Answer 1

MySQL 会为每个返回的行调用此函数四次，为了避免这种情况，您可以使用子查询，所以不用

  SELECT * 
FROM   song 
ORDER  BY Substr(pre_calculated_soundex, 1, 1) = 
                    Substr(Soundex("aaaa"), 1, 1) 
                                                 + Substr(pre_calculated_soundex 
                    , 2, 1) = 
                    Substr 
                    (Soundex("aaaa"), 2, 1) 
                    + Substr(pre_calculated_soundex, 3, 1) 
                    = Substr(Soundex("aaaa"), 3, 1) 
                      + Substr(pre_calculated_soundex, 4, 1 
                      ) 
                      = Substr(Soundex("aaaa"), 4, 1) 

你可以做到

SELECT *  from (select *, Soundex("aaaa") as current_soundex from song)
ORDER  BY 
            Substr(pre_calculated_soundex, 1, 1) = Substr(current_soundex , 1, 1) 
          + Substr(pre_calculated_soundex, 2, 1) = Substr(current_soundex , 2, 1) 
          + Substr(pre_calculated_soundex, 3, 1) = Substr(current_soundex , 3, 1) 
          + Substr(pre_calculated_soundex, 4, 1) = Substr(current_soundex , 4, 1)