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`;