pingcap / pingcap/tidb

planner: 'regexp' cannot cache the pattern compile result if the pattern is a plan-cache parameter

Open
#45,101 1 comment 0 reactions 1 assignee Claimed by @qw4990 View on GitHub
component/expression epic/plan-cache sig/planner type/enhancement
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:
image

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.