MySQL 8 Administrator???s Guide

Using an optimizer for indexes

MySQL 8 allows you to create indexes on generated columns. Generated columns are the columns whose values are computed from an expression included in a column definition. Consider the following example where we have defined one generated column, c2, and created an index on that column:

CREATE TABLE t1 (c1 INT, c2 INT AS (c1 + 1) STORED, INDEX (c2));

Based on the previous definition of a table, an optimizer will consider an index of a generated column in the execution plan. In addition to that, if we specify the same expression in the query using the WHERE, GROUP BY, or ORDER BY clauses, then the optimizer will use the index of the generated column. For example, if we execute the following query, then the optimizer ...