sql - MySQL queries are fast when run directly but really slow when run as stored proc

Question

I've been trying to figure out what's wrong with a set of queries I've got and I'm just confused at this point.

It's supposed to be in a stored procedure which gets called by a GUI application.

There's only one "tiny" problem, it's first a simple UPDATE, then an INSERT using a SELECT with a subselect and finally another UPDATE. Running these queries together by hand I get a total execution time of 0.057s, not too shabby.

Now, I try creating a stored procedure with these queries in it and five input variables, I run this procedure and on the first attempt it took 47.096s with subsequent calls to it showing similar execution times (35 to 50s). Running the individual queries from the MySQL Workbench still show execution times of less than 0.1s

There really isn't anything fancy about these queries, so why is the stored procedure taking an eternity to execute while the queries by themselves only take a fraction of a second? Is there some kind of MySQL peculiarity that I'm missing here?

Additional testing results:

It seems that if I run the queries in MySQL Workbench but use variables instead of just putting the values of the variables in the queries it runs just as slow as the stored procedure. So I tried changing the stored procedure to just use static values instead of variables and suddenly it ran blazingly fast. Apparently for some reason using a variable makes it run extremely slow (for example, the first UPDATE query goes from taking approximately 0.98s with three variables to 0.04-0.05s when I use the values of variables directly in the query, regardless of if it's in the stored procedure or running the query directly).

So, the problem isn't the stored procedure, it's something related to my use of variables (which is unavoidable).

Answer 1

I had the same problem. After researching for a while, I found out the problem was the collation issue while MySQL was comparing text.

TL;DR: the table was created in one collation while MySQL "thought" the variable was in another collation. Therefore, MySQL cannot use the index intended for the query.

In my case, the table was created with (latin1, latin1_swedish_ci) collation. To make MySQL to use the index, I had to change the where clause in the stored procedure from

    UPDATE ... WHERE mycolumn = myvariable

to

    UPDATE ... WHERE mycolumn = 
        convert(myvariable using latin1) collate latin1_swedish_ci

After the change, the stored procedure looked something like this:

    CREATE PROCEDURE foo.'bar'()
    BEGIN
        UPDATE mytable SET mycolumn1 = variable1
        WHERE mycolumn2 = 
            convert(variable2 using latin1) collate latin1_swedish_ci
    END;

where (latin1, latin1_swedish_ci) is the same collation that my tableA was created with.

To check if MySQL uses the index or not, you can change the stored procedure to run an explain statement as followed:

    CREATE PROCEDURE foo.'bar'()
    BEGIN
        EXPLAIN SELECT * FROM table WHERE mycolumn2 = variable2
    END;

In my case, the explain result showed that no index was used during the execution of the query.

Note that MySQL may use the index when you run the query alone, but still won't use the index for the same query inside a stored procedure, which maybe because somehow MySQL sees the variable in another collation.

More information on the collation issue can be found here: http://lowleveldesign.wordpress.com/2013/07/19/diagnosing-collation-issue-mysql-stored-procedure/ Back up link: http://www.codeproject.com/Articles/623272/Diagnosing-a-collation-issue-in-a-MySQL-stored-pro