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
MDEV-17079Update documentation on "EXPLAIN" and "EXPLAIN in the Slow Query Log"
Open
relates to
MDEV-407Print EXPLAIN [ANALYZE] in the slow query log
Fixed the problem with order_by.test (it was a trivial bug).
Discussed "EXPLAIN UPDATE shows VIEW names" problem with Sanja
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
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.
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.
Task
Major
MDEV-6393 ANALYZE SELECT crashes in Explain_query::print_explain with a non-existing column
Fixed the problem with I_S by splitting get_all_tables() into the optimizer part and executor part.