Optimizer Use of Generated Column Indexes The generated column,  gc , is defined as the expression  f1 + 1 . The column is also indexed and the optimizer can take that index into account during execution plan construction. In the following query, the WHERE  clause refers to  gc  and the optimizer considers whether the index on that column yields a more efficient plan: SELECT * FROM t1 WHERE gc > 9; The optimizer can use indexes on generated columns to generate execution plans, even in the absence of direct references in queries to those columns by name. This occurs if the  WHERE ,  ORDER BY , or  GROUP BY  clause refers to an expression that matches the definition of some indexed generated column. The following query does not refer directly to  gc  but does use an expression that matches the definition of  gc : SELECT * FROM t1 WHERE f1 + 1 > 9; The optimizer recognizes that the expression  f1 + 1  matches the definition of  gc  and that  gc  is indexed, so it considers that index during execution plan construction. You can see this using  EXPLAIN : In effect, the optimizer has replaced the expression  f1 + 1  with the name of the generated column that matches the expression. That is also apparent in the rewritten query available in the extended  EXPLAIN information displayed by  SHOW WARNINGS : The following restrictions and conditions apply to the optimizer’s use of generated column indexes: For operators other than  BETWEEN  and  IN() , either operand can be replaced by a matching generated column. For  BETWEEN  and  IN() , only the first argument can be replaced by a matching generated column, and the other arguments must have the same result type.  BETWEEN  and  IN()  are not yet supported for comparisons involving JSON values. 1726 •The generated column must be defined as an expression that contains at least a function call or one of the operators mentioned in the preceding item. The expression cannot consist of a simple reference to another column. For example,  gc INT AS (f1) STORED  consists only of a column reference, so indexes on  gc  are not considered. •The optimization applies to these operators:  = ,  < ,  <= ,  > ,  >= ,  BETWEEN , and  IN() . •For a query expression to match a generated column definition, the expression must be identical and it must have the same result type. For example, if the generated column expression is  f1 + 1 , the optimizer does not recognize a match if the query uses  1 + f1 , or if  f1 + 1  (an integer expression) is compared with a string. mysql>  EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 9\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: range possible_keys: gc key: gc key_len: 5 ref: NULL rows: 1 filtered: 100.00 Extra: Using index condition mysql>  SHOW WARNINGS\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select ‘test‘.‘t1‘.‘f1‘ AS ‘f1‘,‘test‘.‘t1‘.‘gc‘ AS ‘gc‘ from ‘test‘.‘t1‘ where (‘test‘.‘t1‘.‘gc‘ > 9)