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

Add flexible optimizer hints support for every setting in the optimizer_switch and more

Details

    • New Feature
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Duplicate
    • N/A
    • Optimizer
    • None

    Description

      MySQL 8 implemented hints that can be embedded into the query text (for example, in the view definition) and used to control every optimizer option available in the optimizer_switch for every table (among other hints). See https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html for more details.

      I think we should do the same, as finding and fixing every place where SET STATEMENT ... FOR may help to get good plan for some problematic view is too complex to be practical. This was already discussed as a large project in MDEV-12980.

      Attachments

        Issue Links

          is duplicated by

          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
            valerii Valerii Kravchuk created issue -
            julien.fritsch Julien Fritsch made changes -
            Field Original Value New Value
            Assignee Ralf Gebhardt [ ralf.gebhardt@mariadb.com ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 124619 ] MariaDB v4 [ 131546 ]
            valerii Valerii Kravchuk added a comment -

            I wonder why it takes so much time to make a decision on this task?

            valerii Valerii Kravchuk added a comment - I wonder why it takes so much time to make a decision on this task?
            ralf.gebhardt Ralf Gebhardt made changes -
            Assignee Ralf Gebhardt [ ralf.gebhardt@mariadb.com ] Sergei Petrunia [ psergey ]
            psergei Sergei Petrunia added a comment - - edited

            In MySQL:

            Hints use this syntax /*+ hint_text */

            Hints are located at the beginning of query blocks or data statements:

            SELECT /*+ hint */ ...
            		 
            UPDATE /*+ hint */ ...

            There is a separate parser for hints. The common syntax is 

            hint_name(args)

            the arguments depend on the hint.

            psergei Sergei Petrunia added a comment - - edited In MySQL: Hints use this syntax /*+ hint_text */ Hints are located at the beginning of query blocks or data statements: SELECT /*+ hint */ ... UPDATE /*+ hint */ ... There is a separate parser for hints. The common syntax is hint_name(args) the arguments depend on the hint.
            psergei Sergei Petrunia added a comment -

            Notes from the optimizer call (most of the points by Monty)

            • We would like to have optimizer hints.
            • MySQL's approach to hint syntax is generally fine
            • Some of MySQL's hints make sense (e.g. SUBQUERY, , some seem unnecessary (e.g. NO_ICP)
            • Some of MySQL's hints are already implemented in non-hint way in MariaDB (e.g. SET STATEMENT implements SET_VAR hint)
            • However this syntax is different from the current hints (e.g. FORCE INDEX). Having to use two different kinds of syntax at the same time is inconvenient. We need to make sure it's possible to use only the new form, that is, the new syntax should allow everything that the old syntax did.
            • Also add a version# check, similar to how it works for comments: /*+version hint */
            psergei Sergei Petrunia added a comment - Notes from the optimizer call (most of the points by Monty) We would like to have optimizer hints. MySQL's approach to hint syntax is generally fine Some of MySQL's hints make sense (e.g. SUBQUERY, , some seem unnecessary (e.g. NO_ICP) Some of MySQL's hints are already implemented in non-hint way in MariaDB (e.g. SET STATEMENT implements SET_VAR hint) However this syntax is different from the current hints (e.g. FORCE INDEX). Having to use two different kinds of syntax at the same time is inconvenient. We need to make sure it's possible to use only the new form, that is, the new syntax should allow everything that the old syntax did. Also add a version# check, similar to how it works for comments: /*+version hint */
            AirFocus AirFocus made changes -
            Description MySQL 8 implemented hints that can be embedded into the query text (for example, in the view definition) and used to control every optimizer option available in the optimizer_switch for every table (among other hints). See https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html for more details.

            I think we should do the same, as finding and fixing every place where SET STATEMENT ... FOR may help to get good plan for some problematic view is too complex to be practical. This was already discussed as a large project in MDEV-12980.             		MySQL 8 implemented hints that can be embedded into the query text (for example, in the view definition) and used to control every optimizer option available in the optimizer\_switch for every table (among other hints). See https://dev.mysql.com/doc/refman/8.0/en/optimizer\-hints.html for more details.

            I think we should do the same, as finding and fixing every place where SET STATEMENT ... FOR may help to get good plan for some problematic view is too complex to be practical. This was already discussed as a large project in MDEV\-12980.
            psergei Sergei Petrunia added a comment -

            ralf.gebhardt, I think this is 2 Dev Sprints.

            psergei Sergei Petrunia added a comment - ralf.gebhardt , I think this is 2 Dev Sprints.
            julien.fritsch Julien Fritsch made changes -
            Issue Type Task [ 3 ] New Feature [ 2 ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Assignee Sergei Petrunia [ psergey ] Michael Widenius [ monty ]
            ralf.gebhardt Ralf Gebhardt made changes -
            Fix Version/s 11.6 [ 29515 ]
            monty Michael Widenius made changes -
            ralf.gebhardt Ralf Gebhardt made changes -
            Fix Version/s 11.7 [ 29815 ]
            Fix Version/s 11.6 [ 29515 ]
            AirFocus AirFocus made changes -
            Labels triage
            mariadb-jira-automation Jira Automation (IT) made changes -
            Zendesk Related Tickets 201690
            Zendesk active tickets 201690
            serg Sergei Golubchik made changes -
            Fix Version/s 11.8 [ 29921 ]
            Fix Version/s 11.7 [ 29815 ]
            julien.fritsch Julien Fritsch made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            serg Sergei Golubchik made changes -
            Assignee Michael Widenius [ monty ] Oleg Smirnov [ JIRAUSER50405 ]
            serg Sergei Golubchik made changes -
            Summary Add flexible optimizer hints support for every setting in the optimizer_switch and more Add flexible optimizer hints support for every setting in the optimizer_switch
            serg Sergei Golubchik made changes -
            Summary Add flexible optimizer hints support for every setting in the optimizer_switch Add flexible optimizer hints support for every setting in the optimizer_switch and more
            serg Sergei Golubchik made changes -
            Fix Version/s 11.9 [ 29945 ]
            Fix Version/s 11.8 [ 29921 ]
            julien.fritsch Julien Fritsch added a comment -

            Per this announcement in the Slack triage channel: https://mariadb.slack.com/archives/C05S0ANJ8BE/p1739890335402039, we will now only use the "triage" label to indicate an ongoing customer-engineering escalation. Also, I will remove it, and if it's still needed, let me know.

            julien.fritsch Julien Fritsch added a comment - Per this announcement in the Slack triage channel: https://mariadb.slack.com/archives/C05S0ANJ8BE/p1739890335402039 , we will now only use the "triage" label to indicate an ongoing customer-engineering escalation. Also, I will remove it, and if it's still needed, let me know.
            julien.fritsch Julien Fritsch made changes -
            Labels triage
            serg Sergei Golubchik made changes -
            Fix Version/s 12.1 [ 29992 ]
            Fix Version/s 12.0 [ 29945 ]
            julien.fritsch Julien Fritsch made changes -
            Fix Version/s N/A [ 14700 ]
            Fix Version/s 12.1 [ 29992 ]
            Resolution Duplicate [ 3 ]
            Status Open [ 1 ] Closed [ 6 ]

            People

              oleg.smirnov Oleg Smirnov
              valerii Valerii Kravchuk
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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