Details
-
Type:
Task
-
Status: Open
-
Priority:
Major
-
Resolution: Unresolved
-
Fix Version/s: 10.1
-
Component/s: None
-
Labels:
Description
We need to support ORDER/GROUP BY in EXPLAIN JSON.
MySQL's way of doing it has some good ideas: ordering_operation and grouping_operation are two different ops that one can see. But it has issues, too:
- ordering_operation is present even when no ordering takes place http://bugs.mysql.com/bug.php?id=74462
- they show "using_temporary_table": true for both duplicates_removal and "ordering_operation" http://bugs.mysql.com/bug.php?id=74661 when execution only uses one temp.table
- they ignore LIMIT.
- ordering_operation is present even when there is no "operation". Is simply using the appropriate index an "ordering operation"?
- duplicate_removal may be shown when no duplicate removal takes place: http://bugs.mysql.com/bug.php?id=74744
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Tracking where ESC_GROUP_BY comes from:
The ESP_EXISTS is set at JOIN::prepare:
if (group_init)
explain_flags.set(ESC_GROUP_BY, ESP_EXISTS);
...
group_list= ORDER_with_src(group_init, ESC_GROUP_BY);
"Using temporary" for a simple GROUP BY query comes from here:
explain_flags.set(tmp_table_group.src, ESP_USING_TMPTABLE);
#0 Explain_format_flags::set (this=0x7ffea8005f40, clause=ESC_GROUP_BY, property=ESP_USING_TMPTABLE) at /home/psergey/dev2/mysql-5.6/sql/opt_explain_format.h:485 #1 0x0000000000793e5a in JOIN::create_intermediate_table (this=0x7ffea8005c08, tab=0x7ffea8033008, tmp_table_fields=0x7ffea8005e50, tmp_table_group=..., save_sum_fields=false) at /home/psergey/dev2/mysql-5.6/sql/sql_executor.cc:244 #2 0x00000000007fa6a6 in JOIN::make_tmp_tables_info (this=0x7ffea8005c08) at /home/psergey/dev2/mysql-5.6/sql/sql_select.cc:4984 #3 0x0000000000995483 in JOIN::optimize (this=0x7ffea8005c08) at /home/psergey/dev2/mysql-5.6/sql/sql_optimizer.cc:1013
Btw, PostgreSQL's EXPLAIN more closely matches my ideas about how it should look:
- no "ordering operation" when ordering is achieved by using an index. There is "sort", which may or may not be present.
- There are multiple kinds of operations to produce DISTINCT results.
- explicit "LIMIT" operation that takes first N rows.
Looking at mysql-5.6:
bool Explain_join::shallow_explain() { if (begin_sort_context(ESC_ORDER_BY, CTX_ORDER_BY)) return true; if (begin_sort_context(ESC_DISTINCT, CTX_DISTINCT)) return true; if (begin_sort_context(ESC_GROUP_BY, CTX_GROUP_BY)) return true; if (begin_sort_context(ESC_BUFFER_RESULT, CTX_BUFFER_RESULT)) return true; for (size_t t= 0, cnt= fmt->is_hierarchical() ? join->primary_tables : join->tables; t < cnt; t++) { if (explain_join_tab(t)) return true; } if (end_sort_context(ESC_BUFFER_RESULT, CTX_BUFFER_RESULT)) return true; if (end_sort_context(ESC_GROUP_BY, CTX_GROUP_BY)) return true; if (end_sort_context(ESC_DISTINCT, CTX_DISTINCT)) return true; if (end_sort_context(ESC_ORDER_BY, CTX_ORDER_BY)) return true; return false; }...
bool Explain_join::explain_join_tab(size_t tab_num) they have: if (first_non_const) { if (begin_simple_sort_context(ESC_ORDER_BY, CTX_SIMPLE_ORDER_BY)) return true; if (begin_simple_sort_context(ESC_DISTINCT, CTX_SIMPLE_DISTINCT)) return true; if (begin_simple_sort_context(ESC_GROUP_BY, CTX_SIMPLE_GROUP_BY)) return true; }you get the idea