Description:
MySQL does not utilise functional indexes for operators like `LIKE'.

How to repeat:
All of the below are executed under:

1. Table schema:

   CREATE TABLE a ( id int NOT NULL AUTO_INCREMENT, a varchar(100) DEFAULT NULL, !hidden!test01!0!0 varchar(100) GENERATED ALWAYS AS (lower(a)) VIRTUAL, PRIMARY KEY (id), KEY test01 ((lower(a))) ) ENGINE=InnoDB AUTO_INCREMENT=122 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

2. Implicit usage does not use the test01 index:
   
   

   mysql> explain select * from a where lower(a) like 'a'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | a | NULL | ALL | NULL | NULL | NULL | NULL | 96 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

   Explicit usage of the virtually generated columns makes use of that index:
   
   

   mysql> explain select * from a where !hidden!test01!0!0 like 'a'; +----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+ | 1 | SIMPLE | a | NULL | range | test01 | tets01 | 403 | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+

Suggested fix:
The optimizer should use the functional indexes more consistently.