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 $$
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 $$