Sergei Petrunia added a comment - 2014-11-01 03:11 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

Sergei Petrunia added a comment - 2014-11-01 16:52 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

Sergei Petrunia added a comment - 2014-11-07 23:30 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.