Description:
I created a simple table and sample data to test a function index using the REVERSE function to support wildcard prefix LIKE search using reversed values trick.
I inserted the values 'abcdef' and 'xyzabc' and provided skew such that only 'abcdef' is selective. But querying it with the REVERSE function does not work. When querying for REVERSE(c1) LIKE 'def%' the functional index does not work.
When doing exact match on reverse, it does work, but that is not very useful for this use case.
How to repeat:
mysql> create table Z(id bigint auto_increment primary key, c1 varchar(30), c2 int, key((reverse(c1)),c2)) engine=innodb;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into Z values (1, 'abcdef', 1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into Z values (2,'xyzabc', 2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 16 rows affected (0.01 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 32 rows affected (0.01 sec)
Records: 32 Duplicates: 0 Warnings: 0
mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 64 rows affected (0.00 sec)
Records: 64 Duplicates: 0 Warnings: 0
mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 128 rows affected (0.01 sec)
Records: 128 Duplicates: 0 Warnings: 0
mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 256 rows affected (0.01 sec)
Records: 256 Duplicates: 0 Warnings: 0
mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 512 rows affected (0.01 sec)
Records: 512 Duplicates: 0 Warnings: 0
mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 1024 rows affected (0.02 sec)
Records: 1024 Duplicates: 0 Warnings: 0
mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 2048 rows affected (0.04 sec)
Records: 2048 Duplicates: 0 Warnings: 0
mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 4096 rows affected (0.08 sec)
Records: 4096 Duplicates: 0 Warnings: 0
mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
^[[AQuery OK, 8192 rows affected (0.17 sec)
Records: 8192 Duplicates: 0 Warnings: 0
mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 16384 rows affected (0.21 sec)
Records: 16384 Duplicates: 0 Warnings: 0
mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 32768 rows affected (0.39 sec)
Records: 32768 Duplicates: 0 Warnings: 0
mysql> insert into Z select NULL, c1, c2 from Z where id != 1;
Query OK, 65536 rows affected (0.77 sec)
Records: 65536 Duplicates: 0 Warnings: 0
mysql> explain select c2 from Z where reverse(c1) like 'fed%';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | Z | NULL | ALL | NULL | NULL | NULL | NULL | 131202 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select c2 from Z where reverse(c1) = 'fedcba';
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | Z | NULL | ref | functional_index | functional_index | 123 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+------------------+------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
Suggested fix:
Support LIKE with functional indexes?
