| 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: | |
| Category: | MySQL Server: Charsets | Severity: | S2 (Serious) |
| Version: | 5.5.10 | OS: | Linux (Debian Lenny, Arch Linux, adm64 and x86) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | character sets, number to string conversion, Stored Functions | ||
[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]
MySQL Verification Team
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]
Valeriy Kravchuk
This can be related to/have the same reason as bug #60025.
[3 Apr 2011 11:54]
Valeriy 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.
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)