[MDEV-34807] Testing of optimizer hints - Jira

Details

Type: Task
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Fix Version/s: None
Component/s: None
Labels:
None

Description

Our implementation of optimizer hints is intended to bring compatibility with the corresponding functionality of MySQL, which is described here.

Currently we have implemented a partial subset of the hints:

hints for join buffering: BNL(), NO_BNL(), BKA(), NO_BKA();
NO_ICP() hint for disabling index condition pushdown;
MRR(), MO_MRR() hint for multi-range reads control;
NO_RANGE_OPTIMIZATION() for disabling range optimization;
QB_NAME() for assigning names for query blocks;
SEMIJOIN() and NO_SEMIJOIN() for semijoin strategy control;
SUBQUERY() for control of subquery execution strategy.

Examples of applying hints to SQL statements can be found at

mysql-test/main/opt_hints.test; mysql-test/main/opt_hint_timeout.test; mysql-test/main/opt_hints_subquery.test

Attachments

Issue Links

split from

MDEV-33281 Implement optimizer hints like in MySQL 8

In Progress

Activity

Oleg Smirnov added a comment - 2024-09-07 14:26

Requirement from this worklog are generally applicable and may be used for testing.

Functional requirements:

Hints must be enclosed into /*+ */ comment.
Hints must be specified after SELECT|INSERT|REPLACE|UPDATE|DELETE key words.
EXPLAIN must understand hints.
Active hints must be printed in EXPLAIN warning.
Subsequent conflicting/duplicating hints are ignored with warning.
Unresolved hints cause warning.

Some key ideas:

Comment syntax will be used for new hints.
The syntax will be /*+ */
Multiple hints may be specified in the same comment. E.g.,
/*+ HINT1(...) HINT2 HINT3(...) */
A statement block can have only one comment containing hints, and that comment must follow the SELECT, UPDATE, INSERT, REPLACE, or DELETE keyword.
Hints specified in the query block affect only current query block if QB_NAME is not specified. If QB NAME is specified for the hint, this hint affects query block with appropriate query block name.
Bad syntax in a hint will cause a warning.
The first of conflicting hints will have effect, subsequent conflicting/duplicating hints are ignored with warning.
Hints in views are not supported.
All existing hint-like things are merged with new hints. In case of duplication or conflict hints in old style are ignored with warning.
MariaDB already supports some hints(USE|FORCE|IGNORE INDEX). They should comply with new style hints. For example,
/*+ NO_INDEX(t1 idx1) */ ... FROM t1 USE INDEX (idx1);
Hints above are conflicting and hints in old style should be ignored with warning. There won't be any change of the old
style hints processing. We just add verification procedure of the result of old style hint processing and new style hints.
For example, for INDEX hints it's TABLE_LIST::process_index_hints() procedure. This procedure sets various key maps according to old style hints. New procedure which verifies populated key maps with new style hints could be added there.
Note: Will be implemented in separate WL.
Hints always have higher priority against system variables.

Multilevel hints are supported for TABLE level.
For instance, BKA/NO_BKA hint:

    SELECT /*+ BKA() */ * FROM t1, t2 ...; //syntax is supported

    SELECT /*+ BKA(t1) BKA(t2) */ * FROM t1, t2 ...; //syntax is supported

    SELECT /*+ BKA(t1, t2) */ * FROM t1, t2 ...; //syntax is supported

Oleg Smirnov added a comment - 2024-09-07 14:26 Requirement from this worklog are generally applicable and may be used for testing. Functional requirements: Hints must be enclosed into /*+ */ comment. Hints must be specified after SELECT|INSERT|REPLACE|UPDATE|DELETE key words. EXPLAIN must understand hints. Active hints must be printed in EXPLAIN warning. Subsequent conflicting/duplicating hints are ignored with warning. Unresolved hints cause warning. Some key ideas: Comment syntax will be used for new hints. The syntax will be /*+ */ Multiple hints may be specified in the same comment. E.g., /*+ HINT1(...) HINT2 HINT3(...) */ A statement block can have only one comment containing hints, and that comment must follow the SELECT, UPDATE, INSERT, REPLACE, or DELETE keyword. Hints specified in the query block affect only current query block if QB_NAME is not specified. If QB NAME is specified for the hint, this hint affects query block with appropriate query block name. Bad syntax in a hint will cause a warning. The first of conflicting hints will have effect, subsequent conflicting/duplicating hints are ignored with warning. Hints in views are not supported. All existing hint-like things are merged with new hints. In case of duplication or conflict hints in old style are ignored with warning. MariaDB already supports some hints(USE|FORCE|IGNORE INDEX). They should comply with new style hints. For example, /*+ NO_INDEX(t1 idx1) */ ... FROM t1 USE INDEX (idx1); Hints above are conflicting and hints in old style should be ignored with warning. There won't be any change of the old style hints processing. We just add verification procedure of the result of old style hint processing and new style hints. For example, for INDEX hints it's TABLE_LIST::process_index_hints() procedure. This procedure sets various key maps according to old style hints. New procedure which verifies populated key maps with new style hints could be added there. Note: Will be implemented in separate WL. Hints always have higher priority against system variables. Multilevel hints are supported for TABLE level. For instance, BKA/NO_BKA hint: SELECT /*+ BKA() */ * FROM t1, t2 ...; //syntax is supported SELECT /*+ BKA(t1) BKA(t2) */ * FROM t1, t2 ...; //syntax is supported SELECT /*+ BKA(t1, t2) */ * FROM t1, t2 ...; //syntax is supported

People

Assignee:: Lena Startseva

Reporter:: Oleg Smirnov

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2024-08-23 15:24

Updated:: 2024-12-09 10:36

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server