2

Assume I have a table like this in MySQL:

CREATE TABLE Foo (
Key INT NOT NULL
, MyCol INT NOT NULL
PRIMARY KEY (Key))

And I execute a statement like this on:

UPDATE Foo 
SET MyCol = 13
WHERE Key = 42

Will MySQL do an INSERT followed by DELETE of this row behind the scenes (and thus, potentially cause a page split) or can the UPDATE happen in place on the row?

Under which conditions do each of these situations happen?

1

I can confirm that the comment by ypercube above is correct :) Here are some additional details:

  • Updates occur in place, with the older version of the columns relocated to UNDO space. The UNDO is used for providing MVCC, but also in the event of a rollback. As InnoDB pages are initially written they are only 15/16ths filled, so there is some free space to reduce page splits.
  • Indexes contain all 'versions' of a column. This is required to be able to perform index-only-scans while still offering MVCC.
  • Exception to this rule is overflow text/blob pages, which are always freshly written and not updated in place.
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.