0

In our environment, we usually add and drop generated columns every now and then. There is a very large table in the database with more than 3 million rows and adding/removing column was extremely slow. We opted to change the generated column from STORED to VIRTUAL, so that it is instantaneous.

However, we hit another road block: When we try to add the VIRTUAL column after a certain column (I believe this is due to application logic) e.g. AFTER ColumnX, the speed is extremely slow again. It takes more than 4-5 hours to just add the generated VIRTUAL column now.

Why would adding the virtual column without AFTER clause be instantaneous while adding AFTER clause slows it to a crawl?

Database: MySQL version 5.7
Table rows: 32,636,254

ALTER TABLE mySchema.myTable
ADD COLUMN `MyColumn` VARCHAR(50) GENERATED ALWAYS AS 
json_unquote(coalesce(json_extract(`int_data`,'$.Item.IRefNum'),
json_extract(`m_dt`,'$.refId'))) Virtual AFTER `ColumnX`;

1 Answer 1

1

As per the official MySQL documentation reordering the columns will rebuild the entire table thus making it an expensive operation. However, this can be performed online without locking the table.

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

enter image description here

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.