pingcap / pingcap/tidb

Support expression index (arithmetic expression) and improve estimation accuracy

Open
#35,290 0 comments 0 reactions 0 assignees View on GitHub
type/enhancement
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

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.