MySQL Bugs: #60025: SUBSTRING inside a stored function works too slow

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

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

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.

related: bug #60687

Bug #60726 was marked as a duplicate of this one.

Possible workaround is to call

set names latin1;

before defining function.

Bug #60983 was marked as a duplicate of this one.

Bug http://bugs.mysql.com/bug.php?id=60687 marked as duplicate of this one.

Alternate workaround useful when you have a lot of stored procedures :

update mysql.proc set collation_connection = db_collation where collation _connection <> db_collation;

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

Bug #60158 was marked as a duplicate of this one.

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.

Bug#59926 was closed as a duplicate of this one.

I tested the scenario on both 5.5.17 and 5.5.18 and it seems to be solved,

Keep Performing,
Moshe Kaplan

Bug #54485 function execution time increases after each execution

was marked as a duplicate.