
CREATE TABLE `t1` (`id` int(11) UNSIGNED,`txt` VARCHAR(255)) ENGINE=InnoDB;


/* 
	millions of rows, wait a minute...
*/
START TRANSACTION;
DELIMITER //
SET @t=0;
REPEAT 
	INSERT INTO t1 VALUES (@t, MD5(RAND()));
	SET @t = @t+1;
	UNTIL @t > 1000000 END REPEAT//
DELIMITER ;
COMMIT;

/* 
	and add index, copy of table t1 
*/
ALTER TABLE t1 ADD PRIMARY KEY (id), ADD INDEX INDEX_1 (txt) ;
CREATE TABLE t2 LIKE t1;
INSERT INTO t2 SELECT * FROM t1;

/* 
	add trigger on t2 (long runtime table)
*/
DELIMITER //
CREATE TRIGGER `t2_before_update` BEFORE UPDATE ON `t2` FOR EACH ROW BEGIN
	IF NEW.txt='' THEN SET NEW.txt = OLD.txt;
	END IF;
END//
DELIMITER ;

/* 
	UPDATE on table without trigger (333.334 ROWs)
	10.6.10 = 00:00:03
	10.6.11 = 00:00:03 
*/
SET @stamp = UNIX_TIMESTAMP();
START TRANSACTION;
UPDATE t1 SET txt='abc' WHERE MOD(id,3)=0;
SET @ct=ROW_COUNT();
COMMIT;
SELECT 'UPDATE t1' AS info, SEC_TO_TIME(UNIX_TIMESTAMP() - @stamp) AS rtime, @ct AS row_ct;

/* 
	UPDATE on table with trigger (333.334 ROWs)
	10.6.10 = 00:00:03
	10.6.11 = 00:09:55 (!!!)
*/
SET @stamp = UNIX_TIMESTAMP();
START TRANSACTION;
UPDATE t2 SET txt='abc' WHERE MOD(id,3)=0;
SET @ct=ROW_COUNT();
COMMIT;
SELECT 'UPDATE t2' AS info, SEC_TO_TIME(UNIX_TIMESTAMP() - @stamp) AS rtime, @ct AS row_ct;

