Details
-
Type:
Task
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Fix Version/s: 10.1.0
-
Component/s: None
-
Labels:
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()
Gliffy Diagrams
Attachments
Issue Links
- relates to
-
MDEV-7117 ANALYZE SELECT produces strange r_filtered value on a query with ORDER BY
-
- Open
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
MDEV-6422
More QA for ANALYZE stmt and JSON
-
- In Progress
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Fixed the problem with I_S by splitting get_all_tables() into the optimizer part and executor part.
Fixed the problem with order_by.test (it was a trivial bug).
Discussed "EXPLAIN UPDATE shows VIEW names" problem with Sanja
Merged with 10.1
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.
Functionality intended for 10.1.0 has been pushed.
test=# explain select count
from one_k where ones < 10;
QUERY PLAN
-------------------------------------------------------------
Aggregate (cost=17.53..17.54 rows=1 width=0)
-> Seq Scan on one_k (cost=0.00..17.50 rows=11 width=0)
Filter: (ones < 10)
(3 rows)
test=# explain analyze select count
from one_k where ones < 10;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Aggregate (cost=17.53..17.54 rows=1 width=0) (actual time=1.449..1.451 rows=1 loops=1)
-> Seq Scan on one_k (cost=0.00..17.50 rows=11 width=0) (actual time=0.037..1.406 rows=10 loops=1)
Filter: (ones < 10)
Total runtime: 1.560 ms
(4 rows)