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?