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.
          • In Progress
          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.
          • In Progress
          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 created issue -
            oleg.smirnov Oleg Smirnov made changes -
            Field Original Value New Value
            oleg.smirnov Oleg Smirnov made changes -
            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:
            {code}
            hint_name([@query_block_name] [strategy [, strategy] ...])
            {code}
            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 [lMySQL documentation|https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html#optimizer-hints-subquery] for details.             		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:
            {code}
            hint_name([@query_block_name] [strategy [, strategy] ...])
            {code}
            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|https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html#optimizer-hints-subquery] for details.
            oleg.smirnov Oleg Smirnov made changes -
            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:
            {code}
            hint_name([@query_block_name] [strategy [, strategy] ...])
            {code}
            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|https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html#optimizer-hints-subquery] for details.             		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:
            {code}
            hint_name([@query_block_name] [strategy [, strategy] ...])
            {code}
            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|https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html#optimizer-hints-subquery] for details.

            [Worklog #8244|https://dev.mysql.com/worklog/task/?id=8244] may be also useful.
            AirFocus AirFocus made changes -
            Summary  Implement subquery optimizer hints Implement subquery optimizer hints
            serg Sergei Golubchik made changes -
            Fix Version/s 11.8 [ 29921 ]
            Fix Version/s 11.7 [ 29815 ]
            oleg.smirnov Oleg Smirnov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            oleg.smirnov Oleg Smirnov made changes -
            Status In Progress [ 3 ] In Testing [ 10301 ]
            oleg.smirnov Oleg Smirnov made changes -
            Assignee Oleg Smirnov [ JIRAUSER50405 ] Elena Stepanova [ elenst ]
            elenst Elena Stepanova made changes -
            Assignee Elena Stepanova [ elenst ] Lena Startseva [ JIRAUSER50478 ]
            psergei Sergei Petrunia made changes -
            serg Sergei Golubchik made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            Labels Preview_11.8
            oleg.smirnov Oleg Smirnov made changes -
            serg Sergei Golubchik made changes -
            Fix Version/s 11.9 [ 29945 ]
            Fix Version/s 11.8 [ 29921 ]
            lstartseva Lena Startseva made changes -
            serg Sergei Golubchik made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            Labels Preview_11.8 Preview_11.8 Preview_12.0
            julien.fritsch Julien Fritsch made changes -

            People

              lstartseva Lena Startseva
              oleg.smirnov Oleg Smirnov
              Votes:
              1 Vote for this issue
              Watchers:
              6 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.