Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-34888

Implement subquery optimizer hints

Details

    Description

      Subquery hints affect whether to use semijoin transformations and which semijoin strategies to permit, and, when semijoins are not used, whether to use subquery materialization or IN-to-EXISTS transformations.

      Syntax of hints that affect semijoin strategies:

      hint_name([@query_block_name] [strategy [, strategy] ...])

      The syntax refers to these terms:

      • hint_name: These hint names are permitted:
      • SEMIJOIN, NO_SEMIJOIN: Enable or disable the named semijoin strategies.
      • strategy: A semijoin strategy to be enabled or disabled. These strategy names are permitted: DUPSWEEDOUT, FIRSTMATCH, LOOSESCAN, MATERIALIZATION.

      For SEMIJOIN hints, if no strategies are named, semijoin is used if possible based on the strategies enabled according to the optimizer_switch system variable. If strategies are named but inapplicable for the statement, DUPSWEEDOUT is used.

      For NO_SEMIJOIN hints, if no strategies are named, semijoin is not used. If strategies are named that rule out all applicable strategies for the statement, DUPSWEEDOUT is used.

      See MySQL documentation for details.

      Worklog #8244 may be also useful.

      Attachments

        Issue Links

          blocks

          New Feature - A new feature of the product, which has yet to be developed. MDEV-34860 Implement MAX_EXECUTION_TIME hint

          • Critical - Crashes, loss of data, severe memory leak.
          • Stalled
          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36169 If strategies in hint SEMIJOIN are named but inapplicable for the statement, DUPSWEEDOUT shoud be used, but is does not work for engines Aria and InnoDB

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36169 If strategies in hint SEMIJOIN are named but inapplicable for the statement, DUPSWEEDOUT shoud be used, but is does not work for engines Aria and InnoDB

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-35483 Docs for optimizer hints

          • Major - Major loss of function.
          • Stalled
          split from

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-33281 Implement optimizer hints like in MySQL 8

          • Critical - Crashes, loss of data, severe memory leak.
          • In Progress

          Activity

            Ascending order - Click to sort in descending order
            oleg.smirnov Oleg Smirnov added a comment -

            The final version is in the branch 

            bb-opt-hints-1

            .

            oleg.smirnov Oleg Smirnov added a comment - The final version is in the branch bb-opt-hints-1 .
            serg Sergei Golubchik added a comment -

            pushed into bb-11.8-MDEV-35504-opt-hints for buildbot to know the branch so that it could use correct builders

            serg Sergei Golubchik added a comment - pushed into bb-11.8- MDEV-35504 -opt-hints for buildbot to know the branch so that it could use correct builders
            oleg.smirnov Oleg Smirnov added a comment -

            Pushed a new branch bb-11.8-MDEV-35504-opt-hints-v2 including the most recent commits to main.

            oleg.smirnov Oleg Smirnov added a comment - Pushed a new branch bb-11.8- MDEV-35504 -opt-hints-v2 including the most recent commits to main .
            lstartseva Lena Startseva added a comment -

            Testing done, ok to push

            lstartseva Lena Startseva added a comment - Testing done, ok to push

            People

              oleg.smirnov Oleg Smirnov
              oleg.smirnov Oleg Smirnov
              Votes:
              1 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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