MySQL ‘copying to tmp table’ while enlarging VARCHAR

alter-tableMySQLvarchar

One user called today because he has tried to enlarge a VARCHAR column (from 20 to 100) in a slightly large table resulting in a write-locked table.

I watched PROCESSLIST and saw that ALTER TABLE sentence was being locked for 5 minutos in 'copy to tmp table' state.

I'm wondering why MySQL needs to copy table to a tmp table to enlarge a VARCHAR.
I mean, isn't supposed that one of the benefits of VARCHAR is that every row only uses needed bytes for this column as opposed to CHAR that uses all available bytes?.

From http://dev.mysql.com/doc/refman/5.1/en/char.html:

VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data. The length prefix indicates the number of bytes in the value

I understood that, while enlarging a CHAR will need to modify all table data to add new bytes in each row, enlarging a VARCHAR will only need modifications in table schema leaving data as is.

Why does MySQL need to copy the table to a tmp table?

Best Answer

This is the paradigm of ALTER TABLE. It is a simpler approach to making changes. Keep in mind that MySQL supports an open storage engine plugin architecture. The same calls to perform every operation fits InnoDB, MyISAM, and other storage engines.

Under the hood, doing

ALTER TABLE tblname MODIFY COLUMN mycolumn VARCHAR(255);

would mechanically work like this

CREATE TABLE tblname_new LIKE tblname;
ALTER TABLE tblname_new MODIFY COLUMN mycolumn VARCHAR(255);
INSERT INTO tblname_new SELECT * FROM tblname;
DROP TABLE tblname;
ALTER TABLE tblname_new RENAME tblname;

This approach is totally storage engine agnostic.

The alternative to this would be for the storage engine to actively manage a list of blocks that can hold new rows.

What happens when you deal with...

CHAR columns

Even if such management was in place, it would never get summoned because none of the fragments would be large enough to hold changed rows since the column had a smaller size before. This would result in appending all the rows to the physical file for the table. Now, the space occupied by the rows before the change would appear before the first of the table after the ALTER TABLE was done. This would be true for expanding CHAR variables.

VARCHAR columns

In the case of expanding a VARCHAR, altering lengths to be larger may require changing from 1 to 2 bytes for every row should you expand a VARCHAR(255) (255 is a 1-byte unsigned number) to VARCHAR(256) (256 is a 2-byte unsigned number). The introduction of that one extra byte would still require vigorous fragmentation block list management just for the sake of accommodating one extra byte. Therefore, actual space management via maintaining a fragmentation block list would be highly impractical for the open storage engine plugin architecture of MySQL. Such management would probably be in Oracle, SQL Server and PostgreSQL.

Related Question