reverse_index_test.sql

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);
ERROR 1146 (42S02): Table 'test.z' doesn't exist
mysql> insert into Z values (1, 'abcdef', 1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into z values (2,'xyzabc', 2);
ERROR 1146 (42S02): Table 'test.z' doesn't exist
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 'def%';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 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)