Downloads

Blog

Virtual columns in MySQL and MariaDB

March 4, 2016

Author

Stephane Combaudon

MariaDB

MySQL

Share this Post:

MariaDB no longer meeting your needs?

Migrate to Percona software for MySQL – an open source, production-ready, and enterprise-grade MySQL alternative.

Learn More

In this blog post, we compare virtual columns in MySQL and MariaDB.

Virtual columns are one of the standout features in MySQL 5.7. They allow you to store a value derived from one or more columns in the same table. This is a powerful way to build functional indexes. The feature has been available in MariaDB for some time, so it’s worth comparing the implementations.

Documentation

The MariaDB documentation is easy to find.

MySQL 5.7 documentation is less obvious, but the best reference is here.

MariaDB documentation does not clearly explain when to use persistent vs virtual columns. Indexes are only supported on persistent columns, but trade-offs could be better documented.

MySQL documentation suggests using virtual columns by default unless computation cost is high. Stored columns are not required for indexing in most cases.

Syntax

Creating a virtual column is similar in both systems:

ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL;

1	ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL;

However, NOT NULL is not supported in MariaDB:

# MariaDB 10.0
MariaDB [db1]&gt; ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL NOT NULL;
ERROR 1064 (42000): syntax error

# MySQL 5.7
mysql&gt; ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL NOT NULL;
Query OK

# MariaDB 10.0

MariaDB [db1]> ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL NOT NULL;

ERROR 1064 (42000): syntax error

# MySQL 5.7

mysql> ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL NOT NULL;

Query OK

Materialized columns also differ:

MariaDB: PERSISTENT
MySQL: STORED

Adding a Virtual Column

MySQL 5.7:

# MySQL 5.7
ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL NOT NULL;
Query OK

# MySQL 5.7

ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL NOT NULL;

Query OK

This is a metadata-only change and executes instantly.

MariaDB:

# MariaDB 10.0
ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL;
Query OK, 0 rows affected (7 min)

# MariaDB 10.0

ALTER TABLE sbtest1 ADD reverse_pad char(60) GENERATED ALWAYS AS (reverse(pad)) VIRTUAL;

Query OK, 0 rows affected (7 min)

This requires a full table rebuild and is not online. Writes may stall during the operation.

Indexing

MariaDB requires virtual columns to be PERSISTENT to be indexed.

MySQL 5.7 allows indexing virtual columns without storing them, except when used as a primary key.

Example:

# MySQL 5.7
ALTER TABLE sbtest1 ADD INDEX k_rev (k, reverse_pad);

# MariaDB 10.0
ALTER TABLE sbtest1 ADD INDEX k_rev (k, reverse_pad);

# MySQL 5.7

ALTER TABLE sbtest1 ADD INDEX k_rev (k, reverse_pad);

# MariaDB 10.0

ALTER TABLE sbtest1 ADD INDEX k_rev (k, reverse_pad);

Differences:

MySQL: Online operation
MariaDB: Blocking operation

Conclusion

While MySQL 5.7 and MariaDB 10 appear similar in supporting virtual columns, their implementations differ significantly in syntax, performance, and operational impact.

MySQL 5.7 provides a more production-friendly implementation, especially for large tables and high-traffic environments.