MySQL/Aurora increase column size behavior



  • 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



    • 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.



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2