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 ...