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

ANALYZE $stmt

Details

    Description

      (Documentation is at https://mariadb.com/kb/en/analyze-statement/)

      == SQL syntax ==

      The new syntax:

      ANALYZE $explainable_stmt

      ANALYZE $stmt will run the $stmt, and produce the output that EXPLAIN $stmt would produce, annotated with info about the query execution.

      == Adjustments to EXPLAIN output ==

      EXPLAIN FORMAT=JSON is easy to extend.

      As for tabular EXPLAIN form, the following columns will be added:

      • loops ( need this?)
      • r_rows
      • r_filtered

      == Implementation at SQL layer ==

      The parser will set LEX::analyze_stmt flag for ANALYZE statements.
      There is LEX::describe which stores flags about EXPLAIN EXTENDED|PARTITIONS
      but it is used to check whether the query is an EXPLAIN or not, and ANALYZE
      command is not an EXPLAIN, because it actually runs the query.

      Note: ANALYZE UPDATE statement actually makes the updates. With SBR, we will
      have to write the statement into the binlog. The slave must be able to execute
      it (I suspect current slave will choke on a statement that produces output).

      == Counting ==

      We will collect two kinds of counters:

      1. Some are counted at SQL level, like filtered%, ICP_filtered, #rows, etc.

      2. Some will be counted deeper inside the engine, like number of disk reads per table.

      The problems with the latter are

      • the counters are global or per-table. We need them to be per-table-instance
        (to handle self-join-like queries correctly)
      • They may be difficult to get from the SQL layer.

      == Getting the counter values ==

      This is where the new SHOW EXPLAIN architecture plays against us.

      The problem is: at the end of JOIN::optimize(), the plan is saved into an
      Explain_select structure, and EXPLAIN output is produced from Explain_select.

      Explain_select object has only "explain" information, it has no connection to
      objects that participate in query execution (like JOIN_TABs, or handler*, etc).

      An apparent solution is to have JOIN::cleanup() save execution data using a
      call that is similar to save_explain_data()

      Attachments

        Issue Links

          causes

          Task - A task that needs to be done. MDEV-17079 Update documentation on "EXPLAIN" and "EXPLAIN in the Slow Query Log"

          • Major - Major loss of function.
          • Open
          relates to

          Task - A task that needs to be done. MDEV-407 Print EXPLAIN [ANALYZE] in the slow query log

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-6382 ANALYZE $stmt and security

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-6388 ANALYZE $stmt output in the slow query log

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-6393 ANALYZE SELECT crashes in Explain_query::print_explain with a non-existing column

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-6394 ANALYZE DELETE .. RETURNING fails with ERROR 2027 Malformed packet

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-6395 ANALYZE UPDATE/DELETE with impossible where does not produce any output

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-6396 ANALYZE INSERT/REPLACE is accepted, but does not produce a plan

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-6397 ANALYZE SELECT/UPDATE/DELETE increments Com_select/update/delete, but not Com_analyze

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-6398 ANALYZE UPDATE does not populate r_rows

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-6400 "ANALYZE SELECT ... INTO @var" doesn't set @var

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-6422 More testing for ANALYZE stmt and JSON

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-7023 Error 2027: Malformed packet and assertion `field_types == 0 || field_types[field_pos] == MYSQL_TYPE_INT24 || field_types[field_pos] == MYSQL_TYPE_LONG' failure in Protocol_text::store_long

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-7024 Assertion `! is_set()' failed in Diagnostics_area::set_eof_status on executing ANALYZE SELECT via PS

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-7025 ANALYZE SELECT/INSERT/UPDATE/DELETE from a view does not check access permissions on the underlying table

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-7027 ANALYZE SELECT/INSERT/UPDATE/DELETE from a view does not check SHOW permission on the view

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-7117 ANALYZE SELECT produces strange r_filtered value on a query with ORDER BY

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-7115 ANALYZE SELECT FROM empty table produces a plan which looks different from EXPLAIN

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

          Activity

            Ascending order - Click to sort in descending order
            View 18 older comments
            psergei Sergei Petrunia added a comment -

            Fixed the problem with I_S by splitting get_all_tables() into the optimizer part and executor part.

            psergei Sergei Petrunia added a comment - Fixed the problem with I_S by splitting get_all_tables() into the optimizer part and executor part.
            psergei Sergei Petrunia added a comment -

            Fixed the problem with order_by.test (it was a trivial bug).
            Discussed "EXPLAIN UPDATE shows VIEW names" problem with Sanja

            psergei Sergei Petrunia added a comment - Fixed the problem with order_by.test (it was a trivial bug). Discussed "EXPLAIN UPDATE shows VIEW names" problem with Sanja
            psergei Sergei Petrunia added a comment -

            Merged with 10.1

            psergei Sergei Petrunia added a comment - Merged with 10.1
            psergei Sergei Petrunia added a comment -

            As for "EXPLAIN UPDATE shows VIEW names":

            • In EXPLAIN output, tables from views show their aliases inside the VIEWs (as expected).

            In MySQL 5.6, they don't have table->pos_in_table_list->alias == v1. instead, they have table->pos_in_table_list->alias=t1. Grepping the source code for DT_MERGE_FOR_INSERT (or for DT_MERGE or related terms) finds nothing, so I assume they have re-worked derived table merge algorithm and so do not have this "special kind of merge" problem.

            psergei Sergei Petrunia added a comment - As for "EXPLAIN UPDATE shows VIEW names": In EXPLAIN output, tables from views show their aliases inside the VIEWs (as expected). In MySQL 5.6, they don't have table->pos_in_table_list->alias == v1. instead, they have table->pos_in_table_list->alias=t1. Grepping the source code for DT_MERGE_FOR_INSERT (or for DT_MERGE or related terms) finds nothing, so I assume they have re-worked derived table merge algorithm and so do not have this "special kind of merge" problem.
            psergei Sergei Petrunia added a comment -

            Functionality intended for 10.1.0 has been pushed.

            psergei Sergei Petrunia added a comment - Functionality intended for 10.1.0 has been pushed.

            People

              psergei Sergei Petrunia
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.