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.