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`;
Tom V
  • 15,752
  • 7
  • 66
  • 87
Ali
  • 345
  • 3
  • 17

1 Answers1

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

Ali
  • 345
  • 3
  • 17