ANALYZE $stmt

Description

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

== SQL syntax ==

The new syntax:

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()

Assignee

Sergei Petrunia

Reporter

Sergei Petrunia

Fix versions

Priority

Major
Configure