CODEWITHSUNDEEP
mysqlsqlstored-procedures
mludd
mludd

Reputation: 729

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

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

Upvotes: 13

Views: 13747

Answers (5)

Nguyen Phan Tan
Nguyen Phan Tan

Reputation: 419

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

Upvotes: 16

Dereleased
Dereleased

Reputation: 10087

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.

Upvotes: 1

Pete Wilson
Pete Wilson

Reputation: 8694

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.

Upvotes: 1

Logarith
Logarith

Reputation: 710

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

Upvotes: 7

mludd
mludd

Reputation: 729

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.

Upvotes: 1

Related Questions