mysql用了变量就变慢,MySQL查询调整-为什么使用变量中的值比使用文字慢得多？...

UPDATE: I've answered this myself below.

I'm trying to fix a performance issue in a MySQL query. What I think I'm seeing, is that assigning the result of a function to a variable, and then running a SELECT with a compare against that variable is relatively slow.

If for testings sake however, I replace the compare to the variable with a compare to the string literal equivalent of what I know that function will return (for a given scenario), then the query runs much faster.

For example:

...

SET @metaphone_val := double_metaphone(p_parameter)); -- double metaphone is user defined

SELECT

SQL_CALC_FOUND_ROWS

t.col1,

t.col2,

...

FROM table t

WHERE

t.pre_set_metaphone_string = @metaphone_val -- OPTION A

t.pre_set_metaphone_string = 'PRN' -- OPTION B (Literal function return value for a given name)

If I use the line in option A, the query is slow.

If I use the line in option B, then the query is fast as you would expect any simple string compare to be.

Why?

解决方案

Was finished writing the question when the answer hit me, so posting anyway for knowledge sharing!

I realised that the return value of the metaphone function was UTF8.

The compare to a latin1 field was obviously incurring a fairly heavy performance overhead.

I replaced the variable assignment with:

SET @metaphone_val:= CONVERT(double_metaphone(p_parameter) USING latin1);

Now the query runs as fast as I would expect.