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.
2 comments:
Anonymous
said...
Frank,
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.
2 comments:
Frank,
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.
Regards,
Heikki
Innobase Oy
Heikki,
thanks!
that clears it up.
Frank
Post a Comment