XMLWordPrintable

    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

            Activity

              People

              Assignee:
              psergey Sergei Petrunia
              Reporter:
              psergey Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: