Avoid IndexMerge double-read for multi-valued index
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
The multi-valued index uses `IndexMerge` operator to fetch data. However, if a composite index can cover needed columns, the operator should avoid reading data(TableRowIDScan) that introduces unnecessary overhead.
```
mysql> CREATE TABLE t1 (a INT, j JSON, INDEX idx((CAST(j AS SIGNED ARRAY)),a));
Query OK, 0 rows affected (0.05 sec)
mysql> EXPLAIN SELECT /*+ use_index_merge(t1, idx) */ a FROM t1 WHERE ((1 member of (j)));
+---------------------------------+---------+-----------+---------------------------------------------------+---------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------------+---------+-----------+---------------------------------------------------+---------------------------------------------+
| Projection_4 | 10.00 | root | | test.t1.a |
| └─IndexMerge_8 | 10.00 | root | | type: union |
| ├─IndexRangeScan_6(Build) | 10.00 | cop[tikv] | table:t1, index:idx(cast(`j` as signed array), a) | range:[1,1], keep order:false, stats:pseudo |
| └─TableRowIDScan_7(Probe) | 10.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+---------------------------------+---------+-----------+---------------------------------------------------+---------------------------------------------+
4 rows in set (0.00 sec)
```
Contributor guide
Assessment
This issue has not been assessed yet.