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

          blocks

          New Feature - A new feature of the product, which has yet to be developed. MDEV-31535 I_S optimization: smart directory listing based on privileges

          • Major - Major loss of function.
          • In Review
          is blocked by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-41164 Assertion `file < 0' failed in select_to_file::~select_to_file upon SELECT INTO OUTFILE from INFORMATION_SCHEMA.TABLES

          • Critical - Crashes, loss of data, severe memory leak.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-41165 Assertion `!is_set() || (m_status == DA_EOF_BULK && is_bulk_op())' failed in Diagnostics_area::set_eof_status upon ANALYZE SELECT from INFORMATION_SCHEMA.TABLES

          • Critical - Crashes, loss of data, severe memory leak.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-41167 Wrong result (missing row) with LIMIT 1 on INFORMATION_SCHEMA.TABLES

          • Critical - Crashes, loss of data, severe memory leak.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-41171 SQL_SELECT_LIMIT and FOUND_ROWS() are not handled correctly by the INFORMATION_SCHEMA.TABLES fast path

          • Critical - Crashes, loss of data, severe memory leak.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-41196 SQL_BUFFER_RESULT on INFORMATION_SCHEMA.TABLES returns empty values for every column

          • Critical - Crashes, loss of data, severe memory leak.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-41199 PROCEDURE ANALYSE() is silently ignored on INFORMATION_SCHEMA.TABLES

          • Critical - Crashes, loss of data, severe memory leak.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-41204 ER_UNKNOWN_ERROR "Commands out of sync" on INFORMATION_SCHEMA.TABLES when running MTR with --cursor-protocol

          • Critical - Crashes, loss of data, severe memory leak.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-41240 SIGSEGV / heap-use-after-free on INFORMATION_SCHEMA.TABLES with an exact TABLE_NAME lookup and a materialised subquery in WHERE

          • Critical - Crashes, loss of data, severe memory leak.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-41241 Assertion `open_tables == 0 && temporary_tables == 0 && derived_tables == 0 && lock == 0 && locked_tables_mode == LTM_NONE && m_reprepare_observer == __null' failed in void THD::restore_backup_open_tables_state(Open_tables_backup *)

          • Critical - Crashes, loss of data, severe memory leak.
          • Open
          is part of

          Task - A task that needs to be done. MDEV-41087 prepare 13.2 preview

          • Blocker - Blocks development and/or testing work, production could not run.
          • In Progress
          relates to

          New Feature - A new feature of the product, which has yet to be developed. MDEV-36285 Storage Engine for Information Schema (SHOW views)

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

          Task - A task that needs to be done. MDEV-31344 I_S optimization: fast status variable lookup

          • Major - Major loss of function.
          • Stalled
          has action item

          DOCS-6657 Loading...

          Activity

            People

              ramesh Ramesh Sivaraman
              serg Sergei Golubchik
              Votes:
              2 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:

                Time Tracking

                  Estimated:
                  Original Estimate - 0d
                  0d
                  Remaining:
                  Remaining Estimate - 0d
                  0d
                  Logged:
                  Time Spent - 6d 7h
                  6d 7h

                  Git Integration

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