Bug #60726 Functions doing implicit int to string conversion called repeatedly slow down
Submitted: 1 Apr 2011 21:01 Modified: 3 Apr 2011 11:54
Reporter: Karel Belohlavek Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Charsets Severity:S2 (Serious)
Version:5.5.10 OS:Linux (Debian Lenny, Arch Linux, adm64 and x86)
Assigned to:
Tags: character sets, number to string conversion, Stored Functions

[1 Apr 2011 21:01] Karel Belohlavek
Description:
Stored functions which do implicit conversion of numeric values to strings get much slower when called repeatedly. Problem probably occurs just if function is defined with collation_connection other than latin1. 

The first version with this bug migth be 5.5.3 (I found it in 5.5.10 and I verified that it was not a problem with 5.5.2); possibly the problem could have something to do with incompatible change in 5.5.3 concerning character set or collation of strings implicitly converted from numeric values.

How to repeat:
Default MySQL 5.5.10 installation with default my-medium.cnf, database 'test', created during installation:

I have a file test.sql with following function definition:

DELIMITER $$
DROP FUNCTION IF EXISTS format_phone_num$$
CREATE FUNCTION format_phone_num(FPN_NUMBER BIGINT(12)) RETURNS CHAR(20) CHARSET cp1250 DETERMINISTIC
BEGIN
  IF( FPN_NUMBER <= 0 ) THEN
    RETURN NULL;
  END IF; 
  RETURN CONCAT( '+', NULLIF( CONCAT_WS( ' ', SUBSTR( FPN_NUMBER, 1, 3 ), SUBSTR( FPN_NUMBER, 4, 3 ), SUBSTR( FPN_NUMBER, 7, 3 ), SUBSTR( FPN_NUMBER, 10, 3 ), SUBSTR( FP
END$$
DELIMITER ;

Now run in MySQL console:

mysql> \. test.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> show function status like 'format_phone_num'\G
*************************** 1. row ***************************
                  Db: test
                Name: format_phone_num
                Type: FUNCTION
             Definer: karel@localhost
            Modified: 2011-04-01 21:20:15
             Created: 2011-04-01 21:20:15
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

mysql> select format_phone_num(420123456789);
+--------------------------------+
| format_phone_num(420123456789) |
+--------------------------------+
| +420 123 456 789               |
+--------------------------------+
1 row in set (0.00 sec)

mysql> do benchmark(1000, format_phone_num(420123456789));
Query OK, 0 rows affected (0.13 sec)

mysql> do benchmark(1000, format_phone_num(420123456789));
Query OK, 0 rows affected (0.41 sec)

mysql> do benchmark(1000, format_phone_num(420123456789));
Query OK, 0 rows affected (1.37 sec)

mysql> do benchmark(1000, format_phone_num(420123456789));
Query OK, 0 rows affected (2.93 sec)

Times get much slower with repeated function call.

Now when I do:

mysql> set names latin1;
Query OK, 0 rows affected (0.00 sec)

mysql> \. test.sql
Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> show function status like 'format_phone_num'\G
*************************** 1. row ***************************
                  Db: test
                Name: format_phone_num
                Type: FUNCTION
             Definer: karel@localhost
            Modified: 2011-04-01 21:30:30
             Created: 2011-04-01 21:30:30
       Security_type: DEFINER
             Comment: 
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

Problem will not occur:

mysql> do benchmark(1000, format_phone_num(420123456789));
Query OK, 0 rows affected (0.09 sec)

mysql> do benchmark(1000, format_phone_num(420123456789));
Query OK, 0 rows affected (0.10 sec)

mysql> do benchmark(1000, format_phone_num(420123456789));
Query OK, 0 rows affected (0.09 sec)

mysql> do benchmark(1000, format_phone_num(420123456789));
Query OK, 0 rows affected (0.11 sec)
[1 Apr 2011 21:05] Karel Belohlavek
Sorry, I trimmed stored function in "how to repeat" section:

CREATE FUNCTION format_phone_num(FPN_NUMBER BIGINT(12)) RETURNS CHAR(20) CHARSET cp1250 DETERMINISTIC
BEGIN
  IF( FPN_NUMBER <= 0 ) THEN
    RETURN NULL;
  END IF;
  RETURN CONCAT( '+', NULLIF( CONCAT_WS( ' ', SUBSTR( FPN_NUMBER, 1, 3 ), SUBSTR( FPN_NUMBER, 4, 3 ), SUBSTR( FPN_NUMBER, 7, 3 ), SUBSTR( FPN_NUMBER, 10, 3 ), SUBSTR( FP
N_NUMBER, 13 ) ), '' ) );
END
[1 Apr 2011 21:43] Miguel Solorzano
Even with different test case check if related to:
http://bugs.mysql.com/bug.php?id=60687?

Thanks.
[1 Apr 2011 22:14] Karel Belohlavek
Both bugs behave differently:
Bug from http://bugs.mysql.com/bug.php?id=60687 does occur if I define procedure specified in testcase while having character_set_connection=latin1. When I do "SET NAMES utf8;" and recreate procedure, times are constant (my database and server character sets are utf8).

Bug reported here does occur if character_set_connection is other than "latin1" when defining stored procedure or function, no matter, what database or server character set is.

Therefore I would suggest these two could be considered different bugs, though of course cause also might be same (hard to tell).
[2 Apr 2011 7:58] Valerii Kravchuk
This can be related to/have the same reason as bug #60025.
[3 Apr 2011 11:54] Valerii Kravchuk
Your function uses substr() a lot, and I've checked test case for bug #60025 - there execution of

set names latin1;

before defining function also allows to workaround the problem and get the same run time for ever call.

So, I consider this a duplicate of bug #60025.