… if you do UPDATE or DELETE or SELECT FOR UPDATE. InnoDB provides REPEATABLE READ for … same result, regardless other session modifications on that table. This makes reads consistent…innodb_locks_unsafe_for_binlog = 1. Disables the gap locks except for foreign-key constraint checking or duplicate-key…
Post: Innodb performance gotcha w Larger queries.
August 28, 2009 By 7 Comments
… was replacing single value UPDATE with multiple value REPLACE (though I also saw the same problem with INSERT ON DUPLICATE KEY UPDATE) As I went… 2 thoughts – either something is going on with parser or it is something nasty going on on Innodb level, so I tried running the… this function so it did not have a bug. INSERT ON DUPLICATE KEY UPDATE, REPLACE, UPDATE all should be affected, though I have not tested…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 7 Comments
…on AUTO-INC. We have recovered the concurrency and the performance but with a small cost. Queries like INSERT … ON DUPLICATE KEY UPDATE produce gaps on…, `name` int(11) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniqname` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 No …
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… me start with most popular transactional storage engine for MySQL – Innodb. Innodb Storage Engine was covered in a lot of talks, many… used. There are probably similar problems in INSERT IGNORE and ON DUPLICATE KEY UPDATE cases. Now In MySQL 5.1+ it is also possible… not-indexed UPDATE which has to lock all rows in Innodb right now) I however did not get exact plans on this one…
Post: How many partitions can you have ?
December 5, 2009 By 18 Comments
… them per insert statement. As I tested the UPDATE path on INSERT OF DUPLICATE KEY UPDATE (adding ON DUPLICATE KEY UPDATE set c=c+1 to my bulk inserts… ON DUPLICATE KEY UPDATE completes in 22 seconds for 1 partition and 250 for 1000 partitions – which is over 10x difference. Both MyISAM and Innodb…
Comment: INSERT ON DUPLICATE KEY UPDATE and summary counters.
September 4, 2011 By 46 Comments
…) values (‘A’) Duplicate entry ‘A’ for key ‘md5′ I think an incredibly useful feature here would be to return the innodb row id of the row which generated the duplicate key error. This would save us a… column. Someone will answer (why not just use on duplicate key update?) Well this is because i want “on duplicate key return unique_row_id” Cheers //Steve
Comment: INSERT ON DUPLICATE KEY UPDATE and REPLACE INTO
January 22, 2007 By 15 Comments
…=innodb; Query OK, 0 rows affected, 1 warning (0.07 sec) mysql> create table c (id int primary key, fk int , foreign key (fk) references p(id) on delete CASCADE ) type=innodb; Query OK, 0 rows affected… #1. mysql> insert into p (id, notid) values (2,20) on duplicate key update notid=20; Query OK, 2 rows affected (0.01 sec…
Comment: INSERT ON DUPLICATE KEY UPDATE and REPLACE INTO
October 26, 2011 By 15 Comments
… a trigger on an innodb table that “dumps” any updates to the table to a myisam table holding a log of updates. The update table has the same primary key than the… that I need in the log). The trigger uses INSERT ON DUPLICATE KEY UPDATE to achieve that. The problem is that I find many…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… a lower bound on the minimum performance level. Soon you will be able to set up computation over ICE, InnoDB, Vectorwise and… a UNIQUE CHECK * on the base table: `origin_airport_id` * storage node result set merge optimization enabled: ON DUPLICATE KEY UPDATE `origin_airport_id`=VALUES…
Comment: InnoDB vs MyISAM vs Falcon benchmarks - part 1
April 9, 2007 By 117 Comments
… CSV file used 11 queries max (5 selects, 6 insert on duplicate key updates). Using InnoDB on all tables, import took 8 hours, as we switched… selects and on updates were using primary key. I’d be interested what made such difference, because I can see that InnoDb should be…
