Bug #60025 SUBSTRING inside a stored function works too slow
Submitted: 9 Feb 2011 0:27 Modified: 13 May 2011 14:04
Reporter: I G Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.5.9, 5.5.11 OS:Any (Windows, Mac OS X)
Assigned to: CPU Architecture:Any
Tags: regression
Triage: Needs Triage: D2 (Serious)

[9 Feb 2011 0:27] I G
Description:
Each time I execute the last Select statement, it takes more time to give the result. (The first time is faster than the second, and the second than the third...)
In 5.1.* versions there was no problem.

How to repeat:
CREATE FUNCTION tst(param varchar(255)) RETURNS int(1)
BEGIN
     declare m_str varchar(255);
     declare m_x int(11);
     set m_x = 1;
     set m_str = 'asdf';
     while m_x <= length(m_str) do
           set m_x = if(substring(m_str,m_x,1)<>'*',m_x,m_x)+1;
     end while;
     return 1; 
END;

create procedure populate()
begin
     declare m_x int(11);
     create temporary table tmp(ff int(11));
     set m_x = 1;
     while m_x <= 1000 do
           insert into tmp values(m_x);
           set m_x = m_x + 1; 
     end while; 
end;

call populate;

select tst(ff) from tmp
[2 Mar 2011 17:37] Valeriy Kravchuk
Indeed, with current 5.5.11 from bzr built on Mac OS X I get (after several runs):

mysql> select tst(ff) from tmp into outfile '/tmp/out.txt';
Query OK, 1000 rows affected (1.37 sec)

mysql> select tst(ff) from tmp into outfile '/tmp/out2.txt';
Query OK, 1000 rows affected (1.60 sec)

mysql> select tst(ff) from tmp into outfile '/tmp/out3.txt';
Query OK, 1000 rows affected (1.92 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.5.11-debug |
+--------------+
1 row in set (0.00 sec)

While with 5.1 I see the almost same execution time:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.57-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> call populate();
Query OK, 1 row affected (0.37 sec)

mysql> select tst(ff) from tmp into outfile '/tmp/out4.txt';
Query OK, 1000 rows affected (0.83 sec)

mysql> select tst(ff) from tmp into outfile '/tmp/out5.txt';
Query OK, 1000 rows affected (0.86 sec)

mysql> select tst(ff) from tmp into outfile '/tmp/out6.txt';
Query OK, 1000 rows affected (0.85 sec)

mysql> select tst(ff) from tmp into outfile '/tmp/out7.txt';
Query OK, 1000 rows affected (0.85 sec)

This is a regression bug in 5.5.x.
[31 Mar 2011 21:04] Shane Bester
related: bug #60687
[3 Apr 2011 11:56] Valeriy Kravchuk
Bug #60726 was marked as a duplicate of this one.

Possible workaround is to call

set names latin1;

before defining function.
[27 Apr 2011 17:40] Valeriy Kravchuk
Bug #60983 was marked as a duplicate of this one.
[27 Apr 2011 18:13] Miguel Solorzano
Bug http://bugs.mysql.com/bug.php?id=60687 marked as duplicate of this one.
[12 May 2011 9:52] Arnaud Adant
Alternate workaround useful when you have a lot of stored procedures :

update mysql.proc set collation_connection = db_collation where collation _connection <> db_collation;
[13 May 2011 14:04] Paul Dubois
Noted in 5.5.13, 5.6.3 changelogs.

The server consumed memory for repeated invocation of some storedprocedures, which was not released until the connection terminated.

CHANGESET - http://lists.mysql.com/commits/136822
[22 May 2011 15:05] Valeriy Kravchuk
Bug #60158 was marked as a duplicate of this one.
[7 Jun 2011 22:53] Vector Thorn
I just wanted to point out that this also seems to happen on procedures that use Temporary Tables. A proc that takes 0.0003 seconds to execute on 5.1 is taking up to THREE minutes to execute on 5.5, and most of them use string variables and Temporary Tables.
[21 Jul 2011 11:34] Davi Arnaut
Bug#59926 was closed as a duplicate of this one.
[23 Nov 2011 20:52] Moshe Kaplan
I tested the scenario on both 5.5.17 and 5.5.18 and it seems to be solved,

Keep Performing,
Moshe Kaplan
[24 Dec 2012 9:03] Erlend Dahl
Bug #54485 function execution time increases after each execution

was marked as a duplicate.