MySQL Query Tuning - Why is using a value from a variable so much slower than using a literal?

Question

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?

Crushing

Answer 1

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.

Crushing

Thanks for this. I was just struggling with a query where I was just setting a variable to a literal string and it was slowing the query down compared to using the string in the query. I added the CONVERT and it was just as good. – Stotinka

Great answer! Similarly, set @v = convert('' using latin1); if you client is utf8 but your table is latin1. – Shibboleth

Thanks for answering your own question here, I just had exactly the same problem even today, 9 years on with MariaDB :-) One thing I don't get is why this has to be so slow. Surely the SQL compiler can see that this is a constant value and so it only needs to perform the conversion once. Just like if I had used a literal string. I don't get why this hasn't been optimized years ago. – Dion