Description:
When Migrating from 5.0 we discovered a speed regression in UPDATE statement.
Mysql 5.0 only updates rows when value set is different than stored.
From Mysql 5.1 each row gets updated even when the set value is the same than the stored value.
Exepted if table contains a on update current timestamp field.
This problem has a massive performance impact on large tables. In our case up to 15x slower.
How to repeat:
Consider the table :
CREATE TABLE `test_handler_update` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`value` varchar(255) DEFAULT NULL,
`flag` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
Poupulate it:
-- ----------------------------
-- Records of test_handler_update
-- ----------------------------
INSERT INTO `test_handler_update` VALUES ('1', '1', '1');
INSERT INTO `test_handler_update` VALUES ('2', '2', '1');
INSERT INTO `test_handler_update` VALUES ('3', '3', '1');
INSERT INTO `test_handler_update` VALUES ('4', '4', '1');
INSERT INTO `test_handler_update` VALUES ('5', '5', '1');
INSERT INTO `test_handler_update` VALUES ('6', '6', '1');
INSERT INTO `test_handler_update` VALUES ('7', '7', '1');
INSERT INTO `test_handler_update` VALUES ('8', '8', '1');
INSERT INTO `test_handler_update` VALUES ('9', '9', '1');
INSERT INTO `test_handler_update` VALUES ('10', '10', '1');
Now you can see the Handler_update variable status:
FLUSH STATUS ;
UPDATE test_handler_update set flag=1 ;
Show SESSION status WHERE Variable_name = 'Handler_update'
Here is the results:
5.0 : Handler_update 0
5.1 : Handler_update 10
5.7 : Handler_update 10
----
This problem disappears when add a on update current_timestamp field to the table.
ALTER TABLE test_handler_update ADD date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
The results became :
5.0 : Handler_update 0
5.1 : Handler_update 0
5.7 : Handler_update 0
Suggested fix:
Only write modified rows
Description: When Migrating from 5.0 we discovered a speed regression in UPDATE statement. Mysql 5.0 only updates rows when value set is different than stored. From Mysql 5.1 each row gets updated even when the set value is the same than the stored value. Exepted if table contains a on update current timestamp field. This problem has a massive performance impact on large tables. In our case up to 15x slower. How to repeat: Consider the table : CREATE TABLE `test_handler_update` ( `id` int(11) NOT NULL AUTO_INCREMENT, `value` varchar(255) DEFAULT NULL, `flag` tinyint(1) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1; Poupulate it: -- ---------------------------- -- Records of test_handler_update -- ---------------------------- INSERT INTO `test_handler_update` VALUES ('1', '1', '1'); INSERT INTO `test_handler_update` VALUES ('2', '2', '1'); INSERT INTO `test_handler_update` VALUES ('3', '3', '1'); INSERT INTO `test_handler_update` VALUES ('4', '4', '1'); INSERT INTO `test_handler_update` VALUES ('5', '5', '1'); INSERT INTO `test_handler_update` VALUES ('6', '6', '1'); INSERT INTO `test_handler_update` VALUES ('7', '7', '1'); INSERT INTO `test_handler_update` VALUES ('8', '8', '1'); INSERT INTO `test_handler_update` VALUES ('9', '9', '1'); INSERT INTO `test_handler_update` VALUES ('10', '10', '1'); Now you can see the Handler_update variable status: FLUSH STATUS ; UPDATE test_handler_update set flag=1 ; Show SESSION status WHERE Variable_name = 'Handler_update' Here is the results: 5.0 : Handler_update 0 5.1 : Handler_update 10 5.7 : Handler_update 10 ---- This problem disappears when add a on update current_timestamp field to the table. ALTER TABLE test_handler_update ADD date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; The results became : 5.0 : Handler_update 0 5.1 : Handler_update 0 5.7 : Handler_update 0 Suggested fix: Only write modified rows