… aggregation, which should produce updates in-place (I will use INSERT .. ON DUPLICATE KEY UPDATE statements for that), so it will produce all good…) ON DUPLICATE KEY UPDATE k=VALUES(k)+k; INSERT INTO sbtest_r100 (id, k) VALUES ($ID/100, $K) ON DUPLICATE KEY UPDATE k=VALUES(k)+k; INSERT INTO…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
… ‘Insert‘ part. 1. With a standard numeric key id, set as a Primary Key, it will probably be quicker, or the same response, on a normal table as against a partitioned table. 2. The Insert… come out of the partition, not the whole table. 3. Insert on Duplicate Key (I dont do replace… ever)… if across partitions, will be…
Comment: Benchmarking Percona Server TokuDB vs InnoDB
May 9, 2013 By 32 Comments
…, so it was mostly INSERT benchmark What did you mean by that? In your tests you ran many INSERT .. ON DUPLICATE KEY UPDATE queries. That…
Comment: Benchmarking Percona Server TokuDB vs InnoDB
May 9, 2013 By 32 Comments
2 Andy. I mean that in most queries (99%) data was only inserted, not updated. I inserted data with mostly unique PK, That is part ON DUPLICATE KEY was not triggered.
Post: Is your MySQL buffer pool warm? Make it sweat!
April 16, 2013 By 6 Comments
…! There are numerous solutions for MySQL high availability. Many rely on MySQL’s asynchronous replication to maintain a warm standby server… the option innodb_fake_changes to prevent INSERTs, UPDATEs, and DELETEs from corrupting the data on the standby server. You will need… step because update statements that match no rows or insert statements that have duplicate key errors may be faster than an actual database…
Post: Repair MySQL 5.6 GTID replication by injecting empty transactions
March 26, 2013 By 1 Comment
… error: Last_SQL_Error: Error ‘Duplicate entry ’4′ for key ‘PRIMARY” on query. Default database: ‘test’. Query: ‘insert into t VALUES(NULL,’salazar’)’ Retrieved…
Post: INSERT ON DUPLICATE KEY UPDATE and summary counters.
May 29, 2006 By 48 Comments
INSERT … ON DUPLICATE KEY UPDATE is very powerful but often forgotten MySQL feature. It … primary key, hits int unsigned not null, last_hit timestamp); insert into ipstat values(inet_aton(’192.168.0.1′),1,now()) on duplicate key… PRIMARY KEY) it would be just incremented and last visit timestamp updated. The benefit of using this feature insted of INSERT + UPDATE…
Post: INSERT ON DUPLICATE KEY UPDATE and REPLACE INTO
January 18, 2007 By 15 Comments
Jonathan Haddad writes about REPLACE INTO and INSERT ON DUPLICATE KEY UPDATE. Really, Why MySQL has both of these, especially both … acting as update this really will insert NULL value, as col1 is undefined at this stage. INSERT ON DUPLICATE KEY UPDATE is newer feature which…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
… secondary UNIQUE KEY. The queries being run against this table were almost exclusively INSERT … ON DUPLICATE KEY UPDATE (INSERT ODKU), with the columns from the INSERT part… an INSERT … ODKU on this table, we see the following: (root@localhost) [test]> insert into update_test (username,host_id) values (‘foo’,1) on duplicate key…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 8 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…) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uniqname` (`name`) ) ENGINE=InnoDB; Insert a value using a LEFT OUTER JOIN: insert into foo(name) select…
