Boost MySQL Query Performance with Generated Virtual Columns

MySQL 5.7 adds a practical feature called Generated (Virtual) Columns , which lets you define columns whose values are computed from other columns but are not physically stored.

Consider a table that contains a date column SimpleDate. Queries often need the day of week, e.g.: SELECT ... WHERE dayofweek(SimpleDate) = 3 ... Even if SimpleDate is indexed, the function prevents the index from being used, leading to full table scans.

A common workaround is to add an extra column SimpleDate_dayofweek, store the result of dayofweek(SimpleDate) there, and index that column. However, this approach has drawbacks:

Reduced write performance because the extra column must be maintained.

Redundant data that consumes additional storage.

Increased code‑maintenance complexity.

Generated virtual columns solve these problems. You can create a virtual column that computes dayofweek(SimpleDate) on the fly, index it, and avoid storing the computed value.

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `SimpleDate` date DEFAULT NULL,
  `SimpleDate_dayofweek` tinyint(4) GENERATED ALWAYS AS (dayofweek(SimpleDate)) VIRTUAL,
  PRIMARY KEY (`id`),
  KEY `SimpleDate_dayofweek` (`SimpleDate_dayofweek`)
) ENGINE=InnoDB;

After creating the virtual column, the query becomes simple and index‑friendly: SELECT ... WHERE SimpleDate_dayofweek = 3 ... The query can now use the index on the virtual column, delivering better performance without the write‑time overhead of a materialized column.

Virtual columns are not stored in each data row; only their metadata resides in system tables, so adding or dropping them does not require rebuilding the whole table.

Note that you cannot create a composite index that includes both a virtual column and a real column.