Bug #30943 UPDATE where key depends on function VERY slow.
Submitted: 11 Sep 2007 6:43 Modified: 11 Sep 2007 6:47
Reporter: Sergey Bezrukov Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: User-defined functions ( UDF ) Severity:S2 (Serious)
Version:5.1.21, 5.0.45 OS:Linux (SuSE-10)
Assigned to:
Tags: update function key slow

[11 Sep 2007 6:43] Sergey Bezrukov
Description:
When i UPDATE big table, where search key depends on function it run VERY SLOW.
And two functions (integer to string[itc] and vice versa[cti])

On 5.1.21 && 5.0.45:
mysql> select cti(itc(100));
+---------------+
| cti(itc(100)) |
+---------------+
|           100 |
+---------------+
1 row in set (0.00 sec)

mysql> update doc010 set search_content='aaa' where id=100;
Query OK, 1 row affected (0.00 sec)

mysql> update doc010 set search_content='bbb' where id=cti(itc(100));
Query OK, 1 row affected (3 min 40.64 sec)
Rows matched: 1  Changed: 1  Warnings: 0

On 5.0.26::
mysql> select cti(itc(100));
+---------------+
| cti(itc(100)) |
+---------------+
|           100 |
+---------------+
1 row in set (0.00 sec)

mysql> update doc010 set search_content='aaa' where id=100;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> update doc010 set search_content='bbb' where id=cti(itc(100));
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

cti and itc simple function, which convert integer to string and vice versa

How to repeat:
Got a big(20Gb) table with text::

CREATE TABLE `doc010` (
  `id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `content` longtext,
  `search_content` longtext,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 

and 2 functions (integer to string and vice versa)::

DROP FUNCTION IF EXISTS `itc` $$
CREATE FUNCTION `itc` (prm1 bigint) RETURNS varchar(33)
BEGIN
  declare a char(33) default NULL;
  declare l int unsigned;
  set l=length(prm1);
  set a=concat(repeat('0',16-l),prm1);
  return concat('I',reverse(hex(encode(a,'123abc'))));
END $$

DROP FUNCTION IF EXISTS `cti` $$
CREATE FUNCTION `cti` (prm1 varchar(33)) RETURNS bigint unsigned
BEGIN
  declare a char(33) default NULL;
  set a=right(prm1,length(prm1)-1);
  return decode(unhex(reverse(a)),'123abc');
END $$
[11 Sep 2007 6:47] Hartmut Holzgraefe
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.

Support on using our products is available both free in our forums at http://forums.mysql.com/ and for a reasonable fee direct from our skilled support engineers at http://www.mysql.com/support/

Hint: your functions are not DETERMINISTIC 

Thank you for your interest in MySQL.