MySQL performance hit of modify column if no change is made

by

I have many huge MySQL tables (hundreds of milions of entries). They all have a field called “x” which needs to have the NOT NULL constraint. Some of them already have the constraint whereas some of them don’t. I generated a script which modifies it for every table, regardless if it already has the constraint or not. My question is, will there be any performance hit if I execute the following statement and the table already has the constraint?

ALTER TABLE `my_table` MODIFY COLUMN `x` datetime(3) NOT NULL;

P.S. I don’t have direct access to these tables, so I am unable to do a test query and see how long it would take.

  • 1

    I expect it to detect that nothing is changed. But can’t you test it on a test database?

    – 

  • 1

    You could also write a script that uses INFORMATION_SCHEMA to find the tables that don’t already have this constraint, and use that to generate ALTER tables just for those tables.

    – 

  • What version of MySQL? Old versions always copied the table over; new versions try to avoid doing the copy.

    – 

https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html says:

When you keep the same data type and [NOT] NULL attribute, only changing the column name, the operation can always be performed online.

I tested this. I created a table with x defined as a nullable column, and filled it with 2 million rows.

I tried your ALTER TABLE:

ALTER TABLE `my_table` MODIFY COLUMN `x` datetime(3) NOT NULL
--------------

Query OK, 0 rows affected (2.30 sec)

This shows that it took 2.3 seconds.

Then I ran the same change again, on a column already defined as NOT NULL:

ALTER TABLE `my_table` MODIFY COLUMN `x` datetime(3) NOT NULL
--------------

Query OK, 0 rows affected (0.01 sec)

This was much quicker, suggesting that it was able to detect that no change was needed.

I can re-test and alternate between changing it to nullable and not-nullable. Every time it changes, it takes a few seconds. Every time it does not change, it does not take time.

Leave a Comment