Jonathan Haddad writes about REPLACE INTO and INSERT ON DUPLICATE KEY UPDATE. Really, Why MySQL has both of these, especially both are non ANSI SQL extensions ? The story here seems to be the following – REPLACE INTO existed forever, at least since MySQL 3.22 and was a way to do replace faster and what is…
Post: A workaround for the performance problems of TEMPTABLE views
May 19, 2010 By 7 Comments
… set (1.64 sec) You can see that MySQL is accessing millions of rows: mysql> explain select * from v2 where c1 = 100….76 sec) Now to the actual workaround. MySQL won’t merge query filters into the view definition, but we can modify the… value in the params table. Place parameters into the params table using the REPLACE statement: mysql> replace into params (`view_name`, `param1_val`, `connection_id…
Comment: INSERT ON DUPLICATE KEY UPDATE and REPLACE INTO
January 22, 2007 By 15 Comments
….00 sec) mysql> select * from c; +—-+——+ | id | fk | +—-+——+ | 1 | 1 | | 2 | 2 | +—-+——+ 2 rows in set (0.00 sec) mysql> replace into p values (1,10); Query OK, 2 rows affected (0.00 sec) mysql> select * from c; +—-+——+ | id…
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 3 Comments
… import it in our database: mysql (employees) > LOAD DATA INFILE ‘/root/recovery-tool/data/salaries.recovery’ REPLACE INTO TABLE `salaries` FIELDS TERMINATED BY….01 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql (employees) > select * from salaries where emp_no=10008; +——–+——–+————+————+ | emp_no…
Post: Announcing Percona Live MySQL Conference and Expo 2012
August 9, 2011 By 11 Comments
…, with thousands of attendees and an up-front investment well into the six figures, is a whole different undertaking. And although… the MySQL ecosystem, they cannot — cannot — replace the traditional MySQL conference and expo. The “and expo” is crucial here — the O’Reilly MySQL conference…
Post: MySQL-Memcached or NOSQL Tokyo Tyrant - part 1
October 15, 2009 By 16 Comments
All to often people force themselves into using a database like MySQL with no thought into whether if its the best solution to…. All to often people force themselves into using a database like MySQL with no thought into whether if its the best solution to… based primary key B.) Update data from that row and replace the stored contents on disk C.) Use the data from…
Post: Living with backups
November 6, 2008 By 28 Comments
… it’s some nightly batch job that just dumps all MySQL tables into a text file or ordinarily copies the binary files… flushing the data out of the cache. It happens by replacing old and unused blocks with the newly read ones. And… a database of such size. Everything is wiped out and replaced by random “garbage” for no good reason. Since the hit…
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
… I actually went ahead and manually edited test.ibd file replacing few bytes so corruption is mild. First I should note… trial and error approach: mysql> insert into test2 select * from test; ERROR 2013 (HY000): Lost connection to MySQL server during query You may…) mysql> insert ignore into test2 select * from test where id>250; ERROR 2013 (HY000): Lost connection to MySQL server during query mysql> insert ignore into…
Post: EXPLAIN EXTENDED can tell you all kinds of interesting things
June 15, 2010 By 7 Comments
…. Because of this, MySQL can compare the constant values before completely formulating the plan. You will notice the MySQL replaces the WHERE clause… test the plan: mysql> insert into j1 values (1); insert into j2 select * from j1; insert into j3 select * from j2; mysql> explain extended select…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 7 Comments
… assigns the new value to the column. Prior to MySQL 5.1.22 InnoDB used a method to access … are even worse with long running queries like INSERT INTO table1 … SELECT … FROM table2. In version 5.1.22… query like a single-row or multi-row INSERT/REPLACE uses a light-weight mutex instead of a table …
