- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
- Related Questions & Answers
- What are the different types of MySQL GENERATED COLUMNS?
- How MySQL virtual GENERATED COLUMNS can work with mathematical expressions?
- How MySQL stored GENERATED COLUMNS can work with mathematical expressions?
- How Can MySQL virtual GENERATED COLUMNS work with built-in functions?
- How can we alter table to add MySQL virtual GENERATED COLUMNS?
- How MySQL stored GENERATED COLUMNS can work with built-in functions?
- How can we alter table to add MySQL stored GENERATED COLUMNS?
- How we have multiple stored GENERATED COLUMNS in MySQL table with CREATE TABLE statement?
- Concatenate columns from different tables in MySQL
- How can we have multiple virtuals GENERATED COLUMNS in MySQL table with CREATE TABLE statement?
- Get MySQL maximum value from 3 different columns?
- Exclude certain columns from SHOW COLUMNS in MySQL?
- How are subkeys generated in Blowfish algorithm?
- Custom sorting using two different columns in MySQL?
- How can we emulate CHECK CONSTRAINT by using MySQL GENERATED COLUMN?