Adding computed column extremely slow

computed-columnmysql-5.7

We are trying to add computed column to a table and this operation is taking more than 20 hours to complete:

json_unquote(coalesce(json_extract(`int_data`,'$.Item.IRefNum'),
    json_extract(`m_dt`,'$.refId')))

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

The table already exists with data in it and can't be removed.

Best Answer

Adding a STORED generated column causes table re-creation (copy).

From Generated Column Operations in the documentation:

Table 14.13 Online DDL Support for Generated Column Operations
Operation In Place Rebuilds Table Permits Concurrent DML Only Modifies Metadata
Adding a STORED column No Yes No No
Modifying STORED column order No Yes No No
Dropping a STORED column Yes Yes Yes No
Adding a VIRTUAL column Yes No Yes Yes
Modifying VIRTUAL column order No Yes No No
Dropping a VIRTUAL column Yes No Yes Yes

Try making the column VIRTUAL instead:

ALTER TABLE table_name 
    ADD COLUMN column_name column_datatype 
    GENERATED ALWAYS AS (column_expression) VIRTUAL,
    ALGORITHM=INPLACE, LOCK=NONE;
Related Question