snippetsqlMinor
How do MySQL 5.7 virtual columns differ from views?
Viewed 0 times
columnsviewsmysqldifferhowfromvirtual
Problem
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.
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.
Solution
Views and virtual columns are similar, in that neither are materialized. i.e. neither store any data or indexes, hence why your
Where they differ is:
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
index_length+data_length remain the same.Where they differ is:
- 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.
- 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).Context
StackExchange Database Administrators Q#124293, answer score: 4
Revisions (0)
No revisions yet.