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.

Regards,

Heikki
Innobase Oy

Frankly Speaking! said...

Heikki,

thanks!

that clears it up.

Frank