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

EXPLAIN new index suggestion mode

Details

    • Task
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • None
    • Optimizer
    • None

    Description

      When optimizer considers different indexes for a query, for every index it needs to know

      • what columns are part of the index
      • the index cardinality
      • records_in_range — number of values within a specific constant range

      Note that the first question doesn't need the index to be created. And the other two can be answered from EITS, so they don't need an index either.

      So, the optimizer should, technically, be able to create an execution plan that uses an index, even if the index does not exist! This could be incorporated into a new explain mode, say EXPLAIN SUGGEST_INDEXES that will return what indexes (if created) would reduce the query execution cost and how.

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-27146 extend EXPLAIN output to include "attached_condition"

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-27781 Expose syntax parse tree with name resolution

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            serg Sergei Golubchik created issue -
            serg Sergei Golubchik made changes -
            Field Original Value New Value
            Description When optimizer considers different indexes for a query, for every index it needs to know
            * what columns are part of the index
            * the index cardinality
            * records_in_range — number of values within a specific constant range

            Note that the first question doesn't need the index to be created. And the other two can be answered from EITS, so they don't need an index either.

            So, the optimizer should, technically, be able to create an execution plan that uses an index, that does not exist! This could be incorporated into a new explain mode, say {{EXPLAIN SUGGEST_INDEXES}} that will return what indexes (if created) would reduce the query execution cost and how.             		When optimizer considers different indexes for a query, for every index it needs to know
            * what columns are part of the index
            * the index cardinality
            * records_in_range — number of values within a specific constant range

            Note that the first question doesn't need the index to be created. And the other two can be answered from EITS, so they don't need an index either.

            So, the optimizer should, technically, be able to create an execution plan that uses an index, even if the index does not exist! This could be incorporated into a new explain mode, say {{EXPLAIN SUGGEST_INDEXES}} that will return what indexes (if created) would reduce the query execution cost and how.
            danblack Daniel Black made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 90863 ] MariaDB v4 [ 130945 ]
            rjasdfiii Rick James added a comment -

            I have a prototype; it takes only a SELECT and generates suggestions for each table in a JOIN.

            With more information, it could

            • Avoid including a TEXT in an index
            • Know what the PRIMARY KEY is – to avoid suggesting an index starting with that
            • Recognize when a LEFT JOIN is really a JOIN
            • Know what is in "*" (of SELECT *) when proposing a "covering" index
            • Know that a table has exactly 1 row or all rows have the same value in a tentative index column
              (and other things).
            rjasdfiii Rick James added a comment - I have a prototype; it takes only a SELECT and generates suggestions for each table in a JOIN. With more information, it could Avoid including a TEXT in an index Know what the PRIMARY KEY is – to avoid suggesting an index starting with that Recognize when a LEFT JOIN is really a JOIN Know what is in "*" (of SELECT *) when proposing a "covering" index Know that a table has exactly 1 row or all rows have the same value in a tentative index column (and other things).
            serg Sergei Golubchik added a comment -

            what kind of a prototype, in SQL?

            serg Sergei Golubchik added a comment - what kind of a prototype, in SQL?
            rjasdfiii Rick James added a comment -

            PHP with regexps. It would probably be much harder with just SQL. A parse tree would help a lot.

            rjasdfiii Rick James added a comment - PHP with regexps. It would probably be much harder with just SQL. A parse tree would help a lot.
            serg Sergei Golubchik made changes -
            danblack Daniel Black made changes -
            Labels energy
            serg Sergei Golubchik made changes -
            Labels energy

            People

              Unassigned Unassigned
              serg Sergei Golubchik
              Votes:
              1 Vote for this issue
              Watchers:
              4 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.