pingcap / pingcap/tidb

planner: make index merge be aware of order index hints

Open
#48,521 0 comments 0 reactions 1 assignee Claimed by @AilinKid View on GitHub
sig/planner type/enhancement
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Enhancement

### mysql index merge hint
[INDEX_MERGE](https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-index-level), [NO_INDEX_MERGE](https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-index-level): Enable or disable the Index Merge access method for the specified table or indexes. For information about this access method, see [Section 8.2.1.3, “Index Merge Optimization”](https://dev.mysql.com/doc/refman/8.0/en/index-merge-optimization.html). These hints apply to all three Index Merge algorithms.

* The [INDEX_MERGE](https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-index-level) hint forces the optimizer to use Index Merge for the specified table using the specified set of indexes. If no index is specified, the optimizer considers all possible index combinations and selects the least expensive one. The hint may be ignored if the index combination is inapplicable to the given statement.

* The [NO_INDEX_MERGE](https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-index-level) hint disables Index Merge combinations that involve any of the specified indexes. If the hint specifies no indexes, Index Merge is not permitted for the table.

### mysql order index hint
[ORDER_INDEX](https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-index-level), [NO_ORDER_INDEX](https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-index-level): Cause MySQL to use or to ignore the specified index or indexes for sorting rows. Equivalent to FORCE INDEX FOR ORDER BY, IGNORE INDEX FOR ORDER BY. Available beginning with MySQL 8.0.20.

### todo
we should make index merge choose index being aware of order index hint,for example
```
select * from t where a = 1 and b = 2 order by c
```
should we use index idx1(a,c) and index idx2(b,c), or use index just (a),(b) instead because of existence of no_order_index(t, idx1, idx2)

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.