Menu
I would like to know if there is a difference in terms of performance between insert ignore and replace orders in MySQL.
I am using MySQL 5.0.31. All my tables are in InnoDB.
210
Similar to deletes and replace into, with this scheme, “insert ignore” can be two orders of magnitude faster than insertions into a B-tree.
Introduction to MySQL INSERT IGNORE statement As the result, no rows are inserted into the table. However, if you use the INSERT IGNORE statement, the rows with invalid data that cause the error are ignored and the rows with valid data are inserted into the table.
However, with the INSERT IGNORE statement, we can prevent such errors from popping up, especially when inserting entries in bulk and such errors can interrupt the flow of insertion. Instead, only a warning is generated. Upon insertion of NULL value where the column has a NOT NULL constraint.
Insert Ignore statement in MySQL has a special feature that ignores the invalid rows whenever we are inserting single or multiple rows into a table. We can understand it with the following explanation, where a table contains a primary key column. The primary key column cannot stores duplicate values into a table.
insert ignore - if key/row exists, skip insertion
replace - if key/row exists, delete the match row, and insert again
So, replace should be slower.
But insert ignore does not do the update
details : http://dev.mysql.com/doc/refman/5.5/en/replace.html
REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted
109
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
