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 will use the index defined on the generated column:
SELECT * FROM t1 WHERE c1 + 1 > 100;Here, the optimizer will identify that the expression is the same as the definition of column c2. We can check it using the EXPLAIN command, as follows:
mysql>...
