How MySQL stored GENERATED COLUMNS are different from MySQL 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.
Published on 22-Feb-2018 11:52:48
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP