planner: 'regexp' cannot cache the pattern compile result if the pattern is a plan-cache parameter
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
See the case below, if the pattern is a plan-cache parameter, the 'regexp' function cannot cache its compile result, which slow down the overall query processing:
```
create table t (a varchar(10));
insert into t values ('00000');
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
INSERT INTO mysql.expr_pushdown_blacklist VALUES('regexp','tikv','');
admin reload expr_pushdown_blacklist;
mysql> select count(a) from t where a regexp '0000';
+----------+
| count(a) |
+----------+
| 524288 |
+----------+
1 row in set (0.08 sec)
mysql> prepare st from 'select count(a) from t where a regexp ?';
mysql> set @a='0000';
mysql> execute st using @a;
+----------+
| count(a) |
+----------+
| 524288 |
+----------+
1 row in set (0.60 sec)
```
The first normal query just takes `80ms`, while the second prepared query takes `600ms`.
Below is another case's CPU profile:
Contributor guide
Assessment
This issue has not been assessed yet.