0

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

1
  • Modify your experiment queries and add ALGORITHM=INPLACE then look at the messages and warnings.
    – Akina
    Jun 1, 2022 at 4:20

1 Answer 1

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.
11
  • Decreasing a VARCHAR must go through all rows and truncate any rows with too much text. Also decreasing the length from 1000 to 50 should rewrite the table and move the column values from external string storage into the table body.
    – Akina
    Jun 1, 2022 at 4:21
  • Sorry, the MySQL version I put was wrong, I have edit there. Do your comment apply to that version?
    – javaTry
    Jun 1, 2022 at 13:41
  • Minor addition: If the row size has to change, it requires a table copy. This can be true for example if you change the nullability of a column, even if you change nothing in the data. Jun 1, 2022 at 14:17
  • @BillKarwin - Would changing to NOT NULL cause setting to the DEFAULT?
    – Rick James
    Jun 1, 2022 at 14:43
  • @Akina - Good point. However, depending on row format and the sizes of the columns, the Varchars may or may not actually be in "off-record".
    – Rick James
    Jun 1, 2022 at 14:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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