MariaDB no longer meeting your needs?
Migrate to Percona software for MySQL – an open source,
production-ready, and enterprise-grade MySQL alternative.
In this blog post, we’ll compare virtual columns in MySQL and MariaDB.
Virtual columns are one of my top features in MySQL 5.7: they can store a value that is derived from one or several other fields in the same table in a new field. It’s a very good way to build a functional index. This feature has been available in MariaDB for some time, so let’s compare the two and see if they are equivalent.
Documentation
The MariaDB documentation is very easy to find.
Finding the documentation for virtual columns in 5.7 is a bit more challenging. Here is the best link I’ve found.
The MariaDB documentation isn’t clear when you should use a persistent column rather than a virtual one. If you read carefully, you’ll see that indexes are only supported on persistent columns, but the pros and cons of both options could have been better presented.
For MySQL, there is one interesting paragraph listing the potential use cases for stored columns and virtual columns. This paragraph is not super visible, but the gist of it is “always use a virtual column except if the value is too expensive to evaluate on the fly.” Note that you don’t need to use a stored column to index it in 5.7.
Syntax
Creating a virtual column is very similar in both systems:
1 | ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL; |
Note that NOT NULL is not supported with MariaDB while it’s allowed in 5.7:
1 2 3 4 5 6 7 8 | # MariaDB 10.0 MariaDB [db1]> ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL NOT NULL; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NOT NULL' at line 1 # 5.7 mysql> ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL NOT NULL; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 |
When creating a materialized virtual column, the syntax is unfortunately not identical: MariaDB has PERSISTENT columns while 5.7 has STORED columns. It doesn’t look like a big deal, but it’s another item to add to a checklist before a migration.
Adding a virtual column
1 2 3 | # 5.7 ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL NOT NULL; Query OK, 0 rows affected (0.03 sec) |
Great! Creating the column is only a metadata change, so it runs nearly instantly whatever the size of the table is.
With MariaDB, it’s quite different:
1 2 3 | # MariaDB 10.0 ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL; Query OK, 0 rows affected (7 min 8.50 sec) |
Yes, a full table rebuild was needed. And if we are running some sysbench insert workload, we can easily see that this is not an online rebuild – for around 1/3 of the schema change, writes were stalled:
Indexing
That’s probably one of the most striking differences: with MariaDB, a column must be PERSISTENT for it to be indexed. This is not necessary in MySQL 5.7. The only situation when an indexed column in 5.7 must be STORED is when it’s a primary key.
When it comes to adding an index on several columns, some being regular columns and some being virtual columns, both versions allow this action:
1 2 3 4 5 6 7 | # 5.7 mysql> ALTER TABLE sbtest1 ADD INDEX k_rev (k, reverse_pad); Query OK, 0 rows affected (2 min 38.14 sec) # MariaDB 10.0 MariaDB [db1]> ALTER TABLE sbtest1 ADD INDEX k_rev (k, reverse_pad); Query OK, 10187085 rows affected (4 min 43.76 sec) |
The big difference though is that adding the index is performed online in 5.7, while it’s a blocking operation in MariaDB 10.
Conclusion
While at first sight MariaDB 10 and MySQL 5.7 offer very similar features with virtual columns, the reality is quite different: for virtual columns in MySQL and MariaDB the syntax is not exactly the same, adding a virtual column is not done the same way and indexing sets different constraints. The MySQL 5.7 implementation seems more polished for production usage with large tables and/or heavy traffic.
There is one more important difference as described (by me) here:
http://blog.webyog.com/2015/11/09/beware-virtual-columns-may-render-backups-unusable/
It is a little bit hard for understanding, but thank you for the article. I am going to try it with MySQL. Hope, everything will be OK!
Hi
Thank you for your nice article.
I translated this into Japanese for users in Japan.
Translated one is as follows
https://yakst.com/ja/posts/3836
If there is any problem, please get in touch with me.
thank you.
Thank you, but it defeats itself in the examples; you cannot just throw around things like
“ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL;”
What is reverse, what is pad?
Actually once you get past the facts that “reverse” is a rarely used function in mysql, and “pad” is an example column name, this is quite good.
Indexes of virtual columns in Mysql 8.0 and likely older is seriously bugged.
Not recommended.
Performance will degrade over time as it doesn’t refresh content with every update resulting in partial fulltable scans.
You can verify that using EXPLAIN syntax and compare the counters with count()