pingcap / pingcap/tidb

Index join should still be used after constant propagation

Open
#34,158 0 comments 0 reactions 1 assignee Claimed by @hawkingrei View on GitHub
affects-6.5 report/customer sig/planner type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement

```
mysql> drop table if exists t, t1;
Query OK, 0 rows affected (0.07 sec)

mysql> create table t(a int, b int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table t1(a int, b int, key(b));
Query OK, 0 rows affected (0.03 sec)

mysql> explain select /*+ INL_JOIN(v1) */ * from t join t1 on t.a = t1.a and t.b = t1.b where t1.b = 1 limit 1;
+--------------------------------------+---------+-----------+----------------------+---------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------------+---------+-----------+----------------------+---------------------------------------------+
| Limit_14 | 1.00 | root | | offset:0, count:1 |
| └─HashJoin_15 | 1.00 | root | | inner join, equal:[eq(test.t.a, test.t1.a)] |
| ├─IndexLookUp_26(Build) | 9.99 | root | | |
| │ ├─IndexRangeScan_23(Build) | 10.00 | cop[tikv] | table:t1, index:b(b) | range:[1,1], keep order:false, stats:pseudo |
| │ └─Selection_25(Probe) | 9.99 | cop[tikv] | | not(isnull(test.t1.a)) |
| │ └─TableRowIDScan_24 | 10.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
| └─TableReader_19(Probe) | 0.80 | root | | data:Selection_18 |
| └─Selection_18 | 0.80 | cop[tikv] | | eq(test.t.b, 1), not(isnull(test.t.a)) |
| └─TableFullScan_17 | 800.80 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+--------------------------------------+---------+-----------+----------------------+---------------------------------------------+
9 rows in set, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1815 | There are no matching table names for (v1) in optimizer hint /*+ INL_JOIN(v1) */ or /*+ TIDB_INLJ(v1) */. Maybe you can use the table alias name |
+---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```
The inner join's equal condition is `equal:[eq(test.t.a, test.t1.a)]`, the condition `t1.b = 1` has been optimized and pruned. So we can use the index for `t1.b` for index join.
Tables `t` and `t1` still have a lot of data after filtering with `b=1`. If index join can be used at this time, the information of `limit` can be pushed down to the outer side to avoid using a large amount of data to construct a hash table.

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.