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

Expose syntax parse tree with name resolution

    XMLWordPrintable

    Details

      Description

      I would like to get the parse tree of a SELECT statement after the names have been resolved. The goal from there is to develop an automated INDEX generator that comes close to optimal for the query.

      Currently, I am experimenting with PHP and regexps and having moderately good success. The name resolution would take care of single-table queries (wherein I can't easily know what is a column versus a keyword, JOINs without fully qualified column names, etc. Also, the parse tree would take care of some edge cases that my regexps don't cover.

      I understand that the MariaDB code builds a tree of objects to be fed to the Optimizer. The addon would extract the attributes that I need: constant/column/keyword, column name, table_id, datatype, etc.

      I see different approaches for hooking this into the server: a plugin or container, or my preference: something similar to "Optimizer Trace" – turn on a flag, and some JSON is made available.

      Yes, I do understand that there are a lot of variations in optimizing indexes – histograms, probes, BKA, MRR, filter percentage, etc. But you might be horrified to hear how many users don't know what an index is or say "but I indexed every column" or have not heard of a 'composite' index or don't know the difference between JOIN and LEFT JOIN or how an auto_increment PK might slow down a query.

      I expect my 'addon' will be a useful crutch for novices – They extract the tree; my tool gives them an INDEX recommendation.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              rjasdfiii Rick James
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.