How do MySQL 5.7 virtual columns differ from views?

Question

Starting reading point: https://www.percona.com/blog/2015/04/29/generated-virtual-columns-in-mysql-5-7-labs/

Running some tests with the most recent percona 5.7 they seem to work well. Explain plans and wall time look pretty good but I'm not seeing any difference in show table status index_length or data_length.

How do you find the true footprint or overhead for using these?

Unlike views they do seem to be faster than bookmarked selects.

Answer 1

Views and virtual columns are similar, in that neither are materialized. i.e. neither store any data or indexes, hence why your index_length+data_length remain the same.

Where they differ is:

1. Virtual columns are created on base tables, and will co-exist with columns which will be material. This can be useful as it has more of a mixing/matching behavior than views.
2. Virtual columns permit indexes (which are materialized). Views do not.

As a side note, another commonly used feature with some overlap here is triggers. It is often better to have a virtual column than a trigger updating legacy columns on insert/update.

In response to this specific question:

How do you find the true footprint or overhead for using these?

There is no footprint, and you will likely not see overhead. Sure, there are hypothetical cases: If you have many virtual columns and SELECT * from a client then more data will be sent across the network (since rows are sent in full; clients don't know they can reconstruct certain data from virtual column definitions).