Asked 7 years ago
29 Jan 2017
Views 3399
sandip

sandip posted

InnoDB vs VIRTUAL vs STORED in MySQL

InnoDB vs VIRTUAL vs STORED in MySQL
What is difference between InnoDb and VIRTUAL and STORED in MySQL ?
Mitul Dabhi

Mitul Dabhi
answered Nov 30 '-1 00:00

wrong comparison InnoDB vs VIRTUAL vs STORED in MySQL
because you cant compare InnoDB with VIRTUAL / STORED
InnoDB is databsae storage engine , and VIRTUAL / STORED are keyword which define how column will be stored , so one compare VIRTUAL / STORED with each other.

VIRTUAL and STORED are keyword which used with mostly create table Query

VIRTUAL and STORED indicates how column values are stored ,

VIRTUAL: Column values are not stored, but are evaluated virtually when rows are read

STORED: Column values are evaluated and stored , so it require disk space

VIRTUAL is evaluated every time and STORED grab from disk space (memory)

VIRTUAL


SELECT CONCAT(first_name,' ',last_name) AS full_name FROM t1;


here full_name is VIRTUAL column
full_name is not stored any where but it only used until query result presist . so its temporary storage

The NDB storage engine does not support indexing of virtual columns.

From MySQL 5.7 Reference Manual

Definition of VIRTUAL in MySQL
A column whose values are computed from an expression included in the column definition. Column values are not stored, but are evaluated when rows are read, immediately after any BEFORE triggers. A virtual column takes no storage. InnoDB supports secondary indexes on generated virtual columns.

Definition of VIRTUAL in MySQL
A column whose values are computed from an expression included in the column definition. Column values are evaluated and stored when rows are inserted or updated. A stored column requires storage space and can be indexed.


Post Answer