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).

We would need to see some code. First wild guess would be something odd you are doing in declaring/handling the variables... but that's a completely *wild* guess without seeing some code. — Andrew Barber, Oct 24 '11 at 10:07
As I said, very simple queries that run really fast on their own, just `UPDATE table SET column = variable WHERE othercolumn >= othervariable OR othercolumn = yetanothervar` type stuff. And the variables are declared in the regular `IN varname COLUMNTYPE(SIZE)` form as parameters for the stored procedure. What baffles me is just that, there is nothing weird or slow (and yes, I'm avoiding showing the code on account of my boss probably being miffed about me doing that). — mludd, Oct 24 '11 at 10:11
I can mention that removing all but the first `UPDATE` query (which itself takes less than 0.05s to run and then running the stored proc still gives an execution time around 1s, and the above comment is a pretty apt description of just how complex that `UPDATE` is... — mludd, Oct 24 '11 at 10:14

Nguyen Phan Tan · Answer 1 · 2017-01-15T18:26:01.667

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

score 7 · Answer 2 · edited May 06 '13 at 18:16

7

I had a similar problem. Running a mysql routine was horrible slow. But a colleague helped me. The problem was that AUTOCOMMIT was true; So every insert into and select was creating a complete transaction. Then I run my routine with

SET autocommit=0;

at the beginning and

SET autocommit=1;

at the end. The performance went from nearly 500s to 4s

edited May 06 '13 at 18:16

Mark

3,123
4
20
31

answered Apr 29 '13 at 10:14

Logarith

690
1
7
27

1

Cool, did you set this inside the procedure? I have some many insert/select operations inside a cursor (with million records), should I set this outside the LOOP? – Jay Jul 12 '19 at 07:36

score 1 · Answer 3 · answered Oct 25 '11 at 08:25

Since I didn't want to waste too much time trying to figure out why using variables in my stored procedures made them extremely slow I decided to employ a fix some people would consider quite ugly. I simply executed each query directly from the data access layer of my application. Not the prettiest way to do it (since a lot of other stuff for this app uses stored procedures) but it works and now the user won't have to wait 40+ seconds for certain actions as they happen almost instantly.

So, not really a solution or explanation of what was happening, but at least it works.

score 0 · Answer 4 · answered Apr 17 '15 at 19:58

Something that we ran across today that makes procedures slow, even when they run very fast as direct queries, is having parameter (or, presumably, variable) names that are the same as column names. The short version is, don't use a parameter name that is the same as one of the columns in the query in which it will be used. For example, if you had a field called account_id and a parameter named the same, change it to something like in_account_id and your run time can go from multiple seconds to hundredths of a second.

score 0 · Answer 5 · edited May 06 '13 at 18:31

0

Upvote for a very interesting and important question. I found this discussion of some of the reasons that a stored procedure might be slow. I'd be interested to see readers' reactions to it.

The main recommendation that I took from the interchange: it helps to add more indexes.

edited May 06 '13 at 18:31

Mike Sherrill 'Cat Recall'

91,602
17
122
185

answered Oct 24 '11 at 10:22

Pete Wilson

8,610
6
39
51

Your answer here had a dead link. I corrected it as best I could, but I had to refer to archive.org. None of the "duplicate" sources I saw included Ronald Bradford's additions. – Mike Sherrill 'Cat Recall' May 06 '13 at 18:37

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

5 Answers5

Linked