Specializing in big data deployments using MySQL / NoSQL Solutions. Topics: [mysql tutorial] [database design] [mysql data types]
[mysql commands]
[mysql dump]
[database development] [mysql training] [mysql scalability] [mysql sharding] [mysql performance tuning]
Friday, January 05, 2007
InnoDB UPDATE
The other day I heard that InnoDB UPDATE statements perform DELETE and INSERTs and REPLACE is therefore more efficient. Now I'm trying to confirm it by finding documentation on it.
no, InnoDB does not convert an UPDATE to a DELETE + INSERT. Though, at the low level in InnoDB, an UPDATE of the PRIMARY KEY causes the same actions as a DELETE + INSERT. This cannot be avoided, because InnoDB uses MVCC: also the old version of the row must remain for some time.
Maybe part of the confusion comes from the fact that MySQL performs some REPLACEs also internally as a DELETE + INSERT. You know that the semantics of a REPLACE in MySQL is always an INSERT or a DELETE + INSERT. Internally, MySQL tries to perform these DELETE + INSERT pairs as an UPDATE.
Frank,
ReplyDeleteno, InnoDB does not convert an UPDATE to a DELETE + INSERT. Though, at the low level in InnoDB, an UPDATE of the PRIMARY KEY causes the same actions as a DELETE + INSERT. This cannot be avoided, because InnoDB uses MVCC: also the old version of the row must remain for some time.
Maybe part of the confusion comes from the fact that MySQL performs some REPLACEs also internally as a DELETE + INSERT. You know that the semantics of a REPLACE in MySQL is always an INSERT or a DELETE + INSERT. Internally, MySQL tries to perform these DELETE + INSERT pairs as an UPDATE.
Regards,
Heikki
Innobase Oy
Heikki,
ReplyDeletethanks!
that clears it up.
Frank