5

Let's say that I have a table t with 4 normal columns n1, n2, n3, n4 and 1 virtual computed column, c1. If I run the following query:

select n1, n2 from t;

, is the value of c1 computed for each row, even though I have not included it in the columns that I am selecting?

According to the offical MySQL documentation here https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html :

VIRTUAL: Column values are not stored, but are evaluated when rows are read, immediately after any BEFORE triggers.

It says, rows are read, not columns are read. Hence, the confusion.

To ground this in reality, here is my actual situation: I am selecting (no where clause) from such a table as described above and it is being really slow (as compared to other similar tables). I am not selecting the computed column, but I am guessing that it is still being computed, causing the slowness.

euphoria83
  • 14,768
  • 17
  • 63
  • 73

1 Answers1

1

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.

VIRTUAL: Column values are not stored, but are evaluated virtually when rows are read unlike in STORED column where values are evaluated and stored thus requiring disk space. VIRTUAL is evaluated every time

select n1, n2 from t;

Here n1 and n2 are VIRTUAL columns. They are not stored but are only used until query results persist. Its temporary storage


In summary

  • Virtual generated columns would not take any disk space.
  • Virtual generated columns are INPLACE operations which means that the table definition is changed without having to recopy all the data.
  • Virtual generated columns the values are calculated on the fly during the reading operation and BEFORE triggers.
David Kariuki
  • 1,522
  • 1
  • 15
  • 30