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

I_S optimization: avoid temp table

    XMLWordPrintable

Details

    Description

      Currently information_schema tables work like:

      1. prepare information_schema table
        • this creates a temporary table
      2. call the information_schema implementation code
        • it sets values using Field::store() and calls schema_table_store_record() per row
        • schema_table_store_record() uses handler::ha_write_row() to store the row in he temporary table
      3. when the temporary table is filled with data, it's used in the query.

      For queries like SELECT f1, f2, ... FROM INFORMATION_SCHEMA.tbl the above adds a lot of overhead. The server can recognize that case, not create a temporary table in the step 1. And modify schema_table_store_record() to send results directly to the client.

      Another case when a temporary table can be avoided is when a subquery with the information schema table is used in a context implying that the result can be only one row (or an error). Like

      SET @a=(SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='xxx')
      

      Attachments

        Issue Links

          Activity

            People

              TheLinuxJedi Andrew Hutchings
              serg Sergei Golubchik
              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.