Support expression index (arithmetic expression) and improve estimation accuracy
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
### 1. Minimal reproduce step (Required)
create table t1(id1 int auto_increment primary key,id2 int,name varchar(10));
insert into t1 values(1,2,'test1');
insert into t1(id2,name) select id2,name from t1;
insert into t1(id2,name) select id2,name from t1;
insert into t1(id2,name) select id2,name from t1;
insert into t1(id2,name) select id2,name from t1;
insert into t1(id2,name) select id2,name from t1;
insert into t1(id2,name) select id2,name from t1;
insert into t1(id2,name) select id2,name from t1;
insert into t1(id2,name) select id2,name from t1;
insert into t1(id2,name) select id2,name from t1;
insert into t1(id2,name) select id2,name from t1;
analyze table t1;
3 rows in set (0.00 sec)
### 2. What did you expect to see? (Required)
1. Can use index scan: CREATE INDEX idx_id1_id2 ON t1(id1-id2, id1, id2);
3. more accurate for returning result(estrows)
### 3. What did you see instead (Required)
explain select * from t1 where id1-id2<0;
mysql:[test] 16:36:43> explain select * from t1 where id1-id2<0;
+-------------------------+---------+-----------+---------------+----------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------+---------+-----------+---------------+----------------------------------------+
| TableReader_7 | 1638.40 | root | | data:Selection_6 |
| └─Selection_6 | 1638.40 | cop[tikv] | | lt(minus(test.t1.id1, test.t1.id2), 0) |
| └─TableFullScan_5 | 2048.00 | cop[tikv] | table:t1 | keep order:false |
+-------------------------+---------+-----------+---------------+----------------------------------------+
Contributor guide
Assessment
This issue has not been assessed yet.