Difference Between MySQL Stored and Virtual Generated Columns

Followings are some basic differences between MySQL stored GENERATED COLUMNS and MySQL virtual GENERATED COLUMNS −

In terms of Disk Space

If we see the difference in terms of disk space then virtual generated columns would not take any disk space. On the other hand, the stored generated column would take disk space.

In terms of operation

If we see the difference in terms of operation then virtual generated columns are INPLACE operations which means that the table definition is changed without having to recopy all the data again. On the other hand, stored generated columns are a copy operation and it has the same cost as of adding a new column to the table.

In terms of calculations

 In virtual generated columns the values are calculated on the fly during the reading operation and BEFORE triggers. On the other hand, in stored generated columns values are updated in every INSERT and UPDATE statement.

When to use

we should have to consider using virtual columns for data where changes happen in a significant number of times. The cost of a Virtual Column comes from reading a table constantly and the server has to compute every time what that column value will be. On the other hand, we should consider using Stored Columns for when the data doesn’t change significantly or at all after creation.