MySQL Bugs: #24035: performance of update query difference between 5.0.22-log AND 5.0.27-log

Bug #24035	performance of update query difference between 5.0.22-log AND 5.0.27-log
Submitted:	7 Nov 2006 8:57	Modified:	27 Feb 2007 20:23
Reporter:	Rik Druten	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	5.0.34-BK, 5.0.26, 5.0.27	OS:	Linux (Linux, FREEBSD)
Assigned to:	Igor Babaev	CPU Architecture:	Any
Tags:	5.0.24a, regression

Description:
A degredation of performance (dramatically after updating).
Difference in WHERE part '' VS 0.

Following table:

CREATE TABLE `gebruikerslogboek` (
  `gebruikerslogboek_id` int(11) NOT NULL auto_increment,
  `gebruikersnaam` varchar(10) NOT NULL default '',
  `login` datetime NOT NULL default '0000-00-00 00:00:00',
  `logout` datetime default NULL,
  `ip` varchar(16) NOT NULL default '',
  PRIMARY KEY  (`gebruikerslogboek_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=644501 ;

IN 5.0.22 update gebruikerslogboek set gebruikersnaam='test' where
gebruikerslogboek_id='';

executes in 0.00 SEC.

IN 5.0.26 update gebruikerslogboek set gebruikersnaam='test' where
gebruikerslogboek_id='';

executes in 4.15 SEC.

when altering the qry where part: '' becomes 0

IN 5.0.26 update gebruikerslogboek set gebruikersnaam='test' where
gebruikerslogboek_id=0;

performance is as expected.

How to repeat:
CREATE TABLE `gebruikerslogboek` (
  `gebruikerslogboek_id` int(11) NOT NULL auto_increment,
  `gebruikersnaam` varchar(10) NOT NULL default '',
  `login` datetime NOT NULL default '0000-00-00 00:00:00',
  `logout` datetime default NULL,
  `ip` varchar(16) NOT NULL default '',
  PRIMARY KEY  (`gebruikerslogboek_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=644501 ;

fill the table with  644,511 rows...

Also in 5.0.27. I have a serious performance problem. Tomorow i'll test more.

5.0.24a is not affected. I have "normal performance" for this
qry:

update test set b='test' where a='';

but in 5.0.26 and .27 the performance decrease is "not good" or expected.

CREATE TABLE `test` (
  `a` int(11) NOT NULL default '0',
  `b` varchar(10) NOT NULL,
  PRIMARY KEY  (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `test` (`a`, `b`) VALUES (1, 'test'),
(2, 'test'),
(3, 'rik'),
(4, 'rik'),
(5, 'rik'),
(6, 'wim'),
(7, 'rik');

etc ...Table consists of 600000 rows...

This problem occurs with values other than '' as well.

CREATE TABLE `request_log` (
  `request_id` int(10) unsigned NOT NULL auto_increment,
  `user_id` varchar(12) default NULL,
  `time_stamp` datetime NOT NULL default '0000-00-00 00:00:00',
  `ip_address` varchar(15) default NULL,
  PRIMARY KEY  (`request_id`),
  KEY `user_id_2` (`user_id`,`time_stamp`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

mysql> update request_log set user_id = null where id = 999999999999999999999999999999;
Query OK, 0 rows affected (5.15 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update request_log set user_id = null where id = 0;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update request_log set user_id = null where id = '';
Query OK, 0 rows affected (5.10 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select * from request_log where request_id = '';
Empty set (0.00 sec)

mysql> explain select * from request_log where request_id = ''\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Impossible WHERE noticed after reading const tables
1 row in set (0.00 sec)

Running this same query under 5.0.20a, 5.0.21, etc, does not exhibit the problem. Only 5.0.26 and 5.0.27.

Thank you for a bug report. Verified just as described with latest 5.0.34-BK on Linux:

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.34-debug |
+--------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `request_log` (
    ->   `request_id` int(10) unsigned NOT NULL auto_increment,
    ->   `user_id` varchar(12) default NULL,
    ->   `time_stamp` datetime NOT NULL default '0000-00-00 00:00:00',
    ->   `ip_address` varchar(15) default NULL,
    ->   PRIMARY KEY  (`request_id`),
    ->   KEY `user_id_2` (`user_id`,`time_stamp`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.07 sec)

mysql> insert into request_log (user_id) values ('user1');
Query OK, 1 row affected (0.01 sec)

mysql> insert into request_log(user_id) select user_id from request_log;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into request_log(user_id) select user_id from request_log;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

...

mysql> insert into request_log(user_id) select user_id from request_log;
Query OK, 65536 rows affected (4.79 sec)
Records: 65536  Duplicates: 0  Warnings: 0

mysql> update request_log set user_id = null where request_id = 999999999999999999999999999999;
Query OK, 0 rows affected (1.12 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> select count(*) from request_log where request_id=999999999999999999999999999999;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.03 sec)

mysql> explain extended select count(*) from request_log where request_id=99999
9999999999999999999999999\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Impossible WHERE noticed after reading const tables
1 row in set, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: select count(0) AS `count(*)` from `test`.`request_log` where (`test`.`
request_log`.`request_id` = 999999999999999999999999999999)
1 row in set (0.00 sec)

mysql> update request_log set user_id = null where request_id = '';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

mysql> update request_log set user_id = null where request_id = 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

So, we still have this bug, and it is a regression.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19230

ChangeSet@1.2400, 2007-02-02 01:57:19-08:00, igor@olga.mysql.com +3 -0
  Fix bug #24035.
  This performance degradation for UPDATEs could be observed in the update
  statements for which the search key cannot be converted to any valid
  value of the type of the search column, like for a  the condition
  int_fld=99999999999999999999999999, though it can be guaranteed here
  that there is no row with such a key value.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19285

ChangeSet@1.2400, 2007-02-02 15:22:10-08:00, igor@olga.mysql.com +3 -0
  Fix bug #24035.
  This performance degradation for UPDATEs could be observed in the update
  statements for which the search key cannot be converted to any valid
  value of the type of the search column, like for a  the condition
  int_fld=99999999999999999999999999, though it can be guaranteed here
  that there is no row with such a key value.

The fix has been pushed to 5.0.36 and 5.1.16-beta.

Noted in 5.0.36, 5.1.16 changelogs.