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 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?
            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 */
            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 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.

            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.