Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
3  
According to this Advanced aspects of Inserting and Deleting data in MySQL, the clustered index updates can be done in place while the secondary index no. –  ypercube Jan 16 at 10:21
    
Thanks @ypercube. Please add this as the answer and I will up vote it. –  Thomas Kejser Jan 16 at 11:09

1 Answer 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.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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