MySQL 8 Cookbook

Index on generated columns

An index cannot be used on a column wrapped in a function. Suppose you add an index on hire_date:

mysql> ALTER TABLE employees ADD INDEX(hire_date);Query OK, 0 rows affected (0.93 sec)Records: 0  Duplicates: 0  Warnings: 0

The index on hire_date can be used for queries having hire_date in the WHERE clause:

mysql> EXPLAIN SELECT COUNT(*) FROM employees WHERE hire_date>'2000-01-01'\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: employees   partitions: NULL         type: rangepossible_keys: hire_date          key: hire_date      key_len: 3          ref: NULL         rows: 14     filtered: 100.00        Extra: Using where; Using index1 row in set, 1 warning (0.00 sec)

Instead, if you put hire_date inside a function, MySQL ...