Description:
When querying based on a function MySQL does not use the available functional index when using the LIKE operator.
How to repeat:
mysql [localhost:5679] {msandbox} (test) > select @@version ;
+-----------+
| @@version |
+-----------+
| 8.0.25 |
+-----------+
1 row in set (0.00 sec)
mysql [localhost:5679] {msandbox} (test) > create table t1 (c1 varchar(10));
Query OK, 0 rows affected (0.04 sec)
mysql [localhost:5679] {msandbox} (test) > alter table t1 add index ((lower(c1)));
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost:5679] {msandbox} (test) > explain select * from t1 where lower(c1) = 'A' ;
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | functional_index | functional_index | 43 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql [localhost:5679] {msandbox} (test) > explain select * from t1 where lower(c1) LIKE 'A' ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql [localhost:5679] {msandbox} (test) > explain select * from t1 where lower(c1) LIKE 'A%' ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Suggested fix:
There should be a similar behavior between normal indexes and functionals.
Description: When querying based on a function MySQL does not use the available functional index when using the LIKE operator. How to repeat: mysql [localhost:5679] {msandbox} (test) > select @@version ; +-----------+ | @@version | +-----------+ | 8.0.25 | +-----------+ 1 row in set (0.00 sec) mysql [localhost:5679] {msandbox} (test) > create table t1 (c1 varchar(10)); Query OK, 0 rows affected (0.04 sec) mysql [localhost:5679] {msandbox} (test) > alter table t1 add index ((lower(c1))); Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql [localhost:5679] {msandbox} (test) > explain select * from t1 where lower(c1) = 'A' ; +----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | t1 | NULL | ref | functional_index | functional_index | 43 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql [localhost:5679] {msandbox} (test) > explain select * from t1 where lower(c1) LIKE 'A' ; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql [localhost:5679] {msandbox} (test) > explain select * from t1 where lower(c1) LIKE 'A%' ; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) Suggested fix: There should be a similar behavior between normal indexes and functionals.