MySQL/Aurora increase column size behavior

Question

I would like to increase the size of a column. The column currently is varchar(1000) and will be changed to varchar(2000). Null value is already permitted.

The tables has about 2 millions of records, huge reads and many writes (less than reads).

MySQL/Aurora version = 5.7.mysql_aurora.2.09.3

1 - I wrote this command:

LOCK TABLES mytable WRITE;
ALTER TABLE mytable MODIFY COLUMN description VARCHAR(2000);
UNLOCK TABLES;

This command took about 4 minutes to complete.

2 - I wrote only this:

ALTER TABLE mytable MODIFY COLUMN description VARCHAR(2000);

This command took about 3 seconds to complete.

3 - I wrote this (just for testing purposes):

ALTER TABLE mytable MODIFY COLUMN description VARCHAR(50); -- Decreasing

This command took about 4 minutes to complete.

Questions:

I think that second option is faster because mysql will update only metadata, and "knows" that does not exists any record to check due that it is a increasing operation. Is it right?

I am searching for the best way to update this table without using strategies like copy to another table or another column.

Note: I tested in a separated instance (the same hardware) and simulate the same queries (read and write) for all wrote command.

Edited: Change MySQL Version

Answer 1

• If you are using Engine=MyISAM, you should convert to InnoDB.
• With InnoDB, you probably don't need LOCK TABLES.
• Why not switch to TEXT (in this case)?
• For long-running ALTERs, consider using Percona's pt-online-schema-change.
• Increasing a VARCHAR is faster in new versions of MySQL (including, at least, 8.0) It is faster because it is a "meta" change -- that is, the schema changes, but not the data.
• Decreasing a VARCHAR must go through all rows and truncate any rows with too much text.
• As a general rule, if the data has to change, ALTER must copy the table over.
• Altering the PRIMARY KEY requires restructuring the data's BTree, hence requiring a copy.
• Many changes to secondary indexes can be done in the background, so they appear to be instantaneous.