MySQL Bugs: #15189: Optimise INSERT ... ON DUPLICATE KEY ... for identical column values

Bug #15189	Optimise INSERT ... ON DUPLICATE KEY ... for identical column values
Submitted:	23 Nov 2005 15:59	Modified:	23 Nov 2005 19:16
Reporter:	James Harvard	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	5.0.17-BK	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Currently rows in MyISAM tables are not updated if the new column value(s) would be the same as the current column value(s).

However if an INSERT ON DUPLICATE KEY UPDATE statement updates a row with the same column value as the current column value, then the column is still updated.

Presumably INSERT ON DUPL. KEY could be optimised in the same way that UPDATE is optimised.

How to repeat:
create table t1 (id int not null auto_increment primary key, txt varchar(16)) engine=MyISAM;
insert into t1 (id,txt) values (1,'hello world');

# MyISAM update optimisation
update t1 set txt = 'hello world' where id = 1;
# Returns "0 rows affected", table status update_time value not changed

# INSERT ON DUPLICATE KEY UPDATE not optimised
insert into t1 (id,txt) values (1,'hello world') on duplicate key update txt=values(txt);
# Returns "2 rows affected", table status update_time value updated

Hi!

I was unable to reproduce this bug under both, WinXP and FC4 using 5.0.15:

mysql> drop table t1;
ERROR 1051 (42S02): Unknown table 't1'
mysql> create table t1 (id int not null auto_increment primary key, txt varchar(
16))
    -> engine=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 (id,txt) values (1,'hello world');
Query OK, 1 row affected (0.02 sec)

mysql>
mysql> update t1 set txt = 'hello world' where id = 1;
Query OK, 0 rows affected (0.03 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> insert into t1 (id,txt) values (1,'hello world') on duplicate key update
    -> txt=values(txt);
Query OK, 2 rows affected (0.02 sec)

mysql>

I was _not_ reporting this as a bug. Your results are the same as my results.

I was just pointing out that the behaviour of INSERT ON DUPL. KEY UPDATE is inconsistent with the behaviour of UPDATE.

UPDATE  is optimised so that it doesn't update the table if the new and old column values are the same. INSERT ON DUPL. KEY does not have this optimisation. That is why UPDATE reports "0 rows affected" and INSERT ON DUPL. KEY reports "2 rows affected" even though the value of column 'txt' is still 'hello world'.

HTH,
James Harvard

Thank you for the feature request. Current inconsistent behaviour is verified on 5.0.17-BK.

I don't see how this "optimization" can give any improvement. On a primary key error, IOUDKU simply updates the record under the cursor, without reading its contents. UPDATE, on the contrary, has to first look up the record anyway, that is to retrieve it, so once a record is retrieved there is no reason to write it back if it's the same. So apart from a nice "statistics" for IODKU, the gain is zero.