diff options
| -rw-r--r-- | mysql-test/r/analyze_format_json.result | 14 | ||||
| -rw-r--r-- | mysql-test/r/analyze_stmt_orderby.result | 56 | ||||
| -rw-r--r-- | mysql-test/r/derived_opt.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/explain_json.result | 7 | ||||
| -rw-r--r-- | mysql-test/r/join_cache.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/join_outer.result | 12 | ||||
| -rw-r--r-- | mysql-test/r/join_outer_jcl6.result | 12 | ||||
| -rw-r--r-- | mysql-test/r/mrr_derived_crash_4610.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/subselect4.result | 4 | ||||
| -rw-r--r-- | sql/filesort.h | 2 | ||||
| -rw-r--r-- | sql/sql_analyze_stmt.cc | 72 | ||||
| -rw-r--r-- | sql/sql_analyze_stmt.h | 171 | ||||
| -rw-r--r-- | sql/sql_explain.cc | 156 | ||||
| -rw-r--r-- | sql/sql_explain.h | 77 | ||||
| -rw-r--r-- | sql/sql_select.cc | 122 | ||||
| -rw-r--r-- | sql/sql_window.cc | 1 |
16 files changed, 224 insertions, 494 deletions
diff --git a/mysql-test/r/analyze_format_json.result b/mysql-test/r/analyze_format_json.result index 2e106371c63..a66a3895008 100644 --- a/mysql-test/r/analyze_format_json.result +++ b/mysql-test/r/analyze_format_json.result @@ -685,13 +685,13 @@ ANALYZE "r_used_priority_queue": false, "r_output_rows": 0, "volatile parameter": "REPLACED", - "filesort": { - "r_loops": 1, - "volatile parameter": "REPLACED", - "r_used_priority_queue": false, - "r_output_rows": 0, - "volatile parameter": "REPLACED", - "temporary_table": { + "temporary_table": { + "filesort": { + "r_loops": 1, + "volatile parameter": "REPLACED", + "r_used_priority_queue": false, + "r_output_rows": 0, + "volatile parameter": "REPLACED", "temporary_table": { "table": { "table_name": "t2", diff --git a/mysql-test/r/analyze_stmt_orderby.result b/mysql-test/r/analyze_stmt_orderby.result index be1f01a2a52..244da2f596e 100644 --- a/mysql-test/r/analyze_stmt_orderby.result +++ b/mysql-test/r/analyze_stmt_orderby.result @@ -173,7 +173,6 @@ EXPLAIN "select_id": 1, "filesort": { "temporary_table": { - "function": "buffer", "table": { "table_name": "t0", "access_type": "ALL", @@ -460,13 +459,13 @@ ANALYZE "r_limit": 1, "r_used_priority_queue": true, "r_output_rows": 2, - "filesort": { - "r_loops": 1, - "r_total_time_ms": "REPLACED", - "r_used_priority_queue": false, - "r_output_rows": 6, - "r_buffer_size": "REPLACED", - "temporary_table": { + "temporary_table": { + "filesort": { + "r_loops": 1, + "r_total_time_ms": "REPLACED", + "r_used_priority_queue": false, + "r_output_rows": 6, + "r_buffer_size": "REPLACED", "temporary_table": { "table": { "table_name": "t6", @@ -512,25 +511,28 @@ EXPLAIN "select_id": 1, "filesort": { "temporary_table": { - "function": "buffer", - "table": { - "table_name": "t6", - "access_type": "ALL", - "rows": 5, - "filtered": 100, - "attached_condition": "((t6.b > 0) and (t6.a <= 5))" - }, - "block-nl-join": { - "table": { - "table_name": "t5", - "access_type": "ALL", - "rows": 7, - "filtered": 100 - }, - "buffer_type": "flat", - "buffer_size": "256Kb", - "join_type": "BNL", - "attached_condition": "(t5.a = t6.a)" + "filesort": { + "temporary_table": { + "table": { + "table_name": "t6", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "((t6.b > 0) and (t6.a <= 5))" + }, + "block-nl-join": { + "table": { + "table_name": "t5", + "access_type": "ALL", + "rows": 7, + "filtered": 100 + }, + "buffer_type": "flat", + "buffer_size": "256Kb", + "join_type": "BNL", + "attached_condition": "(t5.a = t6.a)" + } + } } } } diff --git a/mysql-test/r/derived_opt.result b/mysql-test/r/derived_opt.result index 04a76c2cbc8..6e4ea1b5d36 100644 --- a/mysql-test/r/derived_opt.result +++ b/mysql-test/r/derived_opt.result @@ -231,8 +231,8 @@ CREATE VIEW v1 AS SELECT a, MIN(b) AS b FROM t2 GROUP BY a; EXPLAIN SELECT * FROM v1, t1 WHERE v1.b=t1.a ORDER BY v1.a; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 system NULL NULL NULL NULL 1 Using filesort -1 PRIMARY <derived2> ref key0 key0 5 const 1 Using where +1 PRIMARY t1 system NULL NULL NULL NULL 1 +1 PRIMARY <derived2> ref key0 key0 5 const 1 Using where; Using filesort 2 DERIVED t2 ALL NULL NULL NULL NULL 10 Using temporary; Using filesort SELECT * FROM v1, t1 WHERE v1.b=t1.a ORDER BY v1.a; a b a diff --git a/mysql-test/r/explain_json.result b/mysql-test/r/explain_json.result index a42f5af114c..975d0fddf38 100644 --- a/mysql-test/r/explain_json.result +++ b/mysql-test/r/explain_json.result @@ -487,7 +487,6 @@ EXPLAIN "select_id": 2, "filesort": { "temporary_table": { - "function": "buffer", "table": { "table_name": "t1", "access_type": "ALL", @@ -531,7 +530,6 @@ EXPLAIN "select_id": 2, "filesort": { "temporary_table": { - "function": "buffer", "table": { "table_name": "t1", "access_type": "ALL", @@ -576,7 +574,6 @@ EXPLAIN "query_block": { "select_id": 2, "temporary_table": { - "function": "buffer", "table": { "table_name": "t1", "access_type": "ALL", @@ -1133,7 +1130,6 @@ EXPLAIN "having_condition": "(TOP > t2.a)", "filesort": { "temporary_table": { - "function": "buffer", "table": { "table_name": "t2", "access_type": "ALL", @@ -1152,7 +1148,6 @@ EXPLAIN "select_id": 1, "filesort": { "temporary_table": { - "function": "buffer", "table": { "table_name": "t2", "access_type": "ALL", @@ -1182,7 +1177,6 @@ EXPLAIN "select_id": 1, "filesort": { "temporary_table": { - "function": "buffer", "table": { "table_name": "t2", "access_type": "ALL", @@ -1380,7 +1374,6 @@ EXPLAIN "query_block": { "select_id": 1, "temporary_table": { - "function": "buffer", "table": { "table_name": "t1", "access_type": "ALL", diff --git a/mysql-test/r/join_cache.result b/mysql-test/r/join_cache.result index d816b1acf92..c15f96fc2e5 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/join_cache.result @@ -5412,9 +5412,9 @@ WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND t2.a BETWEEN 4 and 5 ORDER BY t2.b; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 system NULL NULL NULL NULL 1 Using filesort +1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t3 system NULL NULL NULL NULL 1 -1 PRIMARY t2 range a,c a 5 NULL 1 Using index condition; Using where +1 PRIMARY t2 range a,c a 5 NULL 1 Using index condition; Using where; Using filesort 1 PRIMARY t4 ref c c 5 test.t2.c 2 Using where; Start temporary; End temporary SELECT * FROM t1,t2 WHERE t2.c IN (SELECT c FROM t3,t4 WHERE t4.a < 10) AND diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result index 820d66b9264..ca544f40aa8 100644 --- a/mysql-test/r/join_outer.result +++ b/mysql-test/r/join_outer.result @@ -1289,8 +1289,8 @@ SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b ) FROM t1 JOIN t2 USING( a ) GROUP BY t1.a WITH ROLLUP; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system NULL NULL NULL NULL 1 Using filesort -1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using where +1 SIMPLE t1 system NULL NULL NULL NULL 1 +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using where; Using filesort SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b ) FROM t1 JOIN t2 USING( a ) GROUP BY t1.a WITH ROLLUP; @@ -1429,8 +1429,8 @@ EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL GROUP BY t2.f1, t2.f2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 Using filesort -1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 1 Using where; Using index +1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 +1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 1 Using where; Using index; Using filesort SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL GROUP BY t2.f1, t2.f2; @@ -1846,8 +1846,8 @@ EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL GROUP BY t2.f1, t2.f2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 Using filesort -1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 1 Using where; Using index +1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 +1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 1 Using where; Using index; Using filesort SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL GROUP BY t2.f1, t2.f2; diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result index 4412f8059dd..3616deaee9c 100644 --- a/mysql-test/r/join_outer_jcl6.result +++ b/mysql-test/r/join_outer_jcl6.result @@ -1300,8 +1300,8 @@ SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b ) FROM t1 JOIN t2 USING( a ) GROUP BY t1.a WITH ROLLUP; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system NULL NULL NULL NULL 1 Using filesort -1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using where +1 SIMPLE t1 system NULL NULL NULL NULL 1 +1 SIMPLE t2 ALL NULL NULL NULL NULL 5 Using where; Using filesort SELECT t1.a, COUNT( t2.b ), SUM( t2.b ), MAX( t2.b ) FROM t1 JOIN t2 USING( a ) GROUP BY t1.a WITH ROLLUP; @@ -1440,8 +1440,8 @@ EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL GROUP BY t2.f1, t2.f2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 Using filesort -1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 1 Using where; Using index +1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 +1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 1 Using where; Using index; Using filesort SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL GROUP BY t2.f1, t2.f2; @@ -1857,8 +1857,8 @@ EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL GROUP BY t2.f1, t2.f2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 Using filesort -1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 1 Using where; Using index +1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 +1 SIMPLE t2 ref PRIMARY PRIMARY 4 const 1 Using where; Using index; Using filesort SELECT * FROM t1 LEFT JOIN t2 ON t2.f1 = t1.f1 WHERE t1.f1 = 4 AND t2.f1 IS NOT NULL AND t2.f2 IS NOT NULL GROUP BY t2.f1, t2.f2; diff --git a/mysql-test/r/mrr_derived_crash_4610.result b/mysql-test/r/mrr_derived_crash_4610.result index 8dcdfda9276..3e38a0d4218 100644 --- a/mysql-test/r/mrr_derived_crash_4610.result +++ b/mysql-test/r/mrr_derived_crash_4610.result @@ -7,8 +7,8 @@ explain select 1 from (select f2, f3, val, count(id) from t4 join t2 left join t3 on 0) top join t1 on f1 = f3 where f3 = 'aaaa' order by val; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 const PRIMARY PRIMARY 12 const 1 Using index; Using filesort -1 PRIMARY <derived2> ref key0 key0 13 const 0 Using where +1 PRIMARY t1 const PRIMARY PRIMARY 12 const 1 Using index +1 PRIMARY <derived2> ref key0 key0 13 const 0 Using where; Using filesort 2 DERIVED t4 ALL NULL NULL NULL NULL 1 2 DERIVED t2 ALL NULL NULL NULL NULL 1 Using join buffer (flat, BNL join) 2 DERIVED t3 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (incremental, BNL join) diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 4cd814468a4..7202a6238b0 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -2003,8 +2003,8 @@ FROM t2 JOIN t3 ON t3.f4 = t2.f4 WHERE t3.f1 = 8 GROUP BY 1, 2; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 system NULL NULL NULL NULL 1 Using filesort -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where +1 PRIMARY t3 system NULL NULL NULL NULL 1 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using filesort 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 3 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL no matching row in const table PREPARE st1 FROM " diff --git a/sql/filesort.h b/sql/filesort.h index b92b87bee2e..16ea89ec55a 100644 --- a/sql/filesort.h +++ b/sql/filesort.h @@ -50,6 +50,8 @@ public: /** true means we are using Priority Queue for order by with limit. */ bool using_pq; + Filesort_tracker *tracker; + Filesort(ORDER *order_arg, ha_rows limit_arg, SQL_SELECT *select_arg): order(order_arg), limit(limit_arg), diff --git a/sql/sql_analyze_stmt.cc b/sql/sql_analyze_stmt.cc index d11c93229b0..58f72d6b8de 100644 --- a/sql/sql_analyze_stmt.cc +++ b/sql/sql_analyze_stmt.cc @@ -69,75 +69,3 @@ void Filesort_tracker::print_json_members(Json_writer *writer) } } - -/* - Report that we are doing a filesort. - @return - Tracker object to be used with filesort -*/ - -Filesort_tracker *Sort_and_group_tracker::report_sorting(THD *thd) -{ - DBUG_ASSERT(cur_action < MAX_QEP_ACTIONS); - - if (total_actions) - { - /* This is not the first execution. Check */ - if (qep_actions[cur_action] != EXPL_ACTION_FILESORT) - { - varied_executions= true; - cur_action++; - if (!dummy_fsort_tracker) - dummy_fsort_tracker= new (thd->mem_root) Filesort_tracker(is_analyze); - return dummy_fsort_tracker; - } - return qep_actions_data[cur_action++].filesort_tracker; - } - - Filesort_tracker *fs_tracker= new(thd->mem_root)Filesort_tracker(is_analyze); - qep_actions_data[cur_action].filesort_tracker= fs_tracker; - qep_actions[cur_action++]= EXPL_ACTION_FILESORT; - - return fs_tracker; -} - - -void Sort_and_group_tracker::report_tmp_table(TABLE *tbl) -{ - DBUG_ASSERT(cur_action < MAX_QEP_ACTIONS); - if (total_actions) - { - /* This is not the first execution. Check if the steps match. */ - // todo: should also check that tmp.table kinds are the same. - if (qep_actions[cur_action] != EXPL_ACTION_TEMPTABLE) - varied_executions= true; - } - - if (!varied_executions) - { - qep_actions[cur_action]= EXPL_ACTION_TEMPTABLE; - // qep_actions_data[cur_action]= .... - } - - cur_action++; -} - - -void Sort_and_group_tracker::report_duplicate_removal() -{ - DBUG_ASSERT(cur_action < MAX_QEP_ACTIONS); - if (total_actions) - { - /* This is not the first execution. Check if the steps match. */ - if (qep_actions[cur_action] != EXPL_ACTION_REMOVE_DUPS) - varied_executions= true; - } - - if (!varied_executions) - { - qep_actions[cur_action]= EXPL_ACTION_REMOVE_DUPS; - } - - cur_action++; -} - diff --git a/sql/sql_analyze_stmt.h b/sql/sql_analyze_stmt.h index d7634bdfb85..2a08a842dfc 100644 --- a/sql/sql_analyze_stmt.h +++ b/sql/sql_analyze_stmt.h @@ -284,174 +284,3 @@ private: ulonglong sort_buffer_size; }; - -typedef enum -{ - EXPL_NO_TMP_TABLE=0, - EXPL_TMP_TABLE_BUFFER, - EXPL_TMP_TABLE_GROUP, - EXPL_TMP_TABLE_DISTINCT -} enum_tmp_table_use; - - -typedef enum -{ - EXPL_ACTION_EOF, /* not-an-action */ - EXPL_ACTION_FILESORT, - EXPL_ACTION_TEMPTABLE, - EXPL_ACTION_REMOVE_DUPS, -} enum_qep_action; - - -/* - This is to track how a JOIN object has resolved ORDER/GROUP BY/DISTINCT - - We are not tied to the query plan at all, because query plan does not have - sufficient information. *A lot* of decisions about ordering/grouping are - made at very late stages (in JOIN::exec, JOIN::init_execution, in - create_sort_index and even in create_tmp_table). - - The idea is that operations that happen during select execution will report - themselves. We have these operations: - - Sorting with filesort() - - Duplicate row removal (the one done by remove_duplicates()). - - Use of temporary table to buffer the result. - - There is also "Selection" operation, done by do_select(). It reads rows, - there are several distinct cases: - 1. doing the join operation on the base tables - 2. reading the temporary table - 3. reading the filesort output - it would be nice to build execution graph, e.g. - - Select(JOIN op) -> temp.table -> filesort -> Select(filesort result) - - the problem is that there is no way to tell what a do_select() call will do. - - Our solution is not to have explicit selection operations. We make these - assumptions about the query plan: - - Select(JOIN op) is the first operation in the query plan - - Unless the first recorded operation is filesort(). filesort() is unable - read result of a select, so when we find it first, the query plan is: - - filesort(first join table) -> Select(JOIN op) -> ... - - the other popular query plan is: - - Select (JOIN op) -> temp.table -> filesort() -> ... - -///TODO: handle repeated execution with subselects! -*/ - -class Sort_and_group_tracker : public Sql_alloc -{ - enum { MAX_QEP_ACTIONS = 5 }; - - /* Query actions in the order they were made. */ - enum_qep_action qep_actions[MAX_QEP_ACTIONS]; - - /* Number for the next action */ - int cur_action; - - /* - Non-zero means there was already an execution which had - #total_actions actions - */ - int total_actions; - - int get_n_actions() - { - return total_actions? total_actions: cur_action; - } - - /* - TRUE<=>there were executions which took different sort/buffer/de-duplicate - routes. The counter values are not meaningful. - */ - bool varied_executions; - - /* Details about query actions */ - union - { - Filesort_tracker *filesort_tracker; - enum_tmp_table_use tmp_table; - } - qep_actions_data[MAX_QEP_ACTIONS]; - - Filesort_tracker *dummy_fsort_tracker; - bool is_analyze; -public: - Sort_and_group_tracker(bool is_analyze_arg) : - cur_action(0), total_actions(0), varied_executions(false), - dummy_fsort_tracker(NULL), - is_analyze(is_analyze_arg) - {} - - /*************** Reporting interface ***************/ - /* Report that join execution is started */ - void report_join_start() - { - if (!total_actions && cur_action != 0) - { - /* This is a second execution */ - total_actions= cur_action; - } - cur_action= 0; - } - - /* - Report that a temporary table is created. The next step is to write to the - this tmp. table - */ - void report_tmp_table(TABLE *tbl); - - /* - Report that we are doing a filesort. - @return - Tracker object to be used with filesort - */ - Filesort_tracker *report_sorting(THD *thd); - - /* - Report that remove_duplicates() is invoked [on a temp. table]. - We don't collect any statistics on this operation, yet. - */ - void report_duplicate_removal(); - - friend class Iterator; - /*************** Statistics retrieval interface ***************/ - bool had_varied_executions() { return varied_executions; } - - class Iterator - { - Sort_and_group_tracker *owner; - int idx; - public: - Iterator(Sort_and_group_tracker *owner_arg) : - owner(owner_arg), idx(owner_arg->get_n_actions() - 1) - {} - - enum_qep_action get_next(Filesort_tracker **tracker/*, - enum_tmp_table_use *tmp_table_use*/) - { - /* Walk back through the array... */ - if (idx < 0) - return EXPL_ACTION_EOF; - switch (owner->qep_actions[idx]) - { - case EXPL_ACTION_FILESORT: - *tracker= owner->qep_actions_data[idx].filesort_tracker; - break; - case EXPL_ACTION_TEMPTABLE: - //*tmp_table_use= tmp_table_kind[tmp_table_idx++]; - break; - default: - break; - } - return owner->qep_actions[idx--]; - } - - bool is_last_element() { return idx == -1; } - }; -}; - diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc index 593a2dc0a88..f3001d5942a 100644 --- a/sql/sql_explain.cc +++ b/sql/sql_explain.cc @@ -697,14 +697,6 @@ bool Explain_node::print_explain_json_cache(Json_writer *writer, } -#if 0 -void Explain_select::replace_table(uint idx, Explain_table_access *new_tab) -{ - delete join_tabs[idx]; - join_tabs[idx]= new_tab; -} -#endif - Explain_basic_join::~Explain_basic_join() { if (join_tabs) @@ -755,35 +747,23 @@ int Explain_select::print_explain(Explain_query *query, } else { - bool using_tmp; - bool using_fs; + bool using_tmp= false; + bool using_fs= false; - if (is_analyze) + for (Explain_aggr_node *node= aggr_tree; node; node= node->child) { - /* - Get the data about "Using temporary; Using filesort" from execution - tracking system. - */ - using_tmp= false; - using_fs= false; - Sort_and_group_tracker::Iterator iter(&ops_tracker); - enum_qep_action action; - Filesort_tracker *dummy; - - while ((action= iter.get_next(&dummy)) != EXPL_ACTION_EOF) + switch (node->get_type()) { - if (action == EXPL_ACTION_FILESORT) - using_fs= true; - else if (action == EXPL_ACTION_TEMPTABLE) + case AGGR_OP_TEMP_TABLE: using_tmp= true; + break; + case AGGR_OP_FILESORT: + using_fs= true; + break; + default: + break; } } - else - { - /* Use imprecise "estimates" we got with the query plan */ - using_tmp= using_temporary; - using_fs= using_filesort; - } for (uint i=0; i< n_join_tabs; i++) { @@ -877,88 +857,34 @@ void Explain_select::print_explain_json(Explain_query *query, } } - Filesort_tracker *first_table_sort= NULL; - bool first_table_sort_used= false; int started_objects= 0; + + Explain_aggr_node *node= aggr_tree; - if (is_analyze) - { - /* ANALYZE has collected this part of query plan independently */ - if (ops_tracker.had_varied_executions()) - { - writer->add_member("varied-sort-and-tmp").start_object(); - started_objects++; - } - else - { - Sort_and_group_tracker::Iterator iter(&ops_tracker); - enum_qep_action action; - Filesort_tracker *fs_tracker; - - while ((action= iter.get_next(&fs_tracker)) != EXPL_ACTION_EOF) - { - if (action == EXPL_ACTION_FILESORT) - { - if (iter.is_last_element()) - { - first_table_sort= fs_tracker; - break; - } - writer->add_member("filesort").start_object(); - started_objects++; - fs_tracker->print_json_members(writer); - } - else if (action == EXPL_ACTION_TEMPTABLE) - { - writer->add_member("temporary_table").start_object(); - started_objects++; - /* - if (tmp == EXPL_TMP_TABLE_BUFFER) - func= "buffer"; - else if (tmp == EXPL_TMP_TABLE_GROUP) - func= "group-by"; - else - func= "distinct"; - writer->add_member("function").add_str(func); - */ - } - else if (action == EXPL_ACTION_REMOVE_DUPS) - { - writer->add_member("duplicate_removal").start_object(); - started_objects++; - } - else - DBUG_ASSERT(0); - } - } - - if (first_table_sort) - first_table_sort_used= true; - } - else + for (; node; node= node->child) { - /* This is just EXPLAIN. Try to produce something meaningful */ - if (using_temporary) + switch (node->get_type()) { - started_objects= 1; - if (using_filesort) + case AGGR_OP_TEMP_TABLE: + writer->add_member("temporary_table").start_object(); + break; + case AGGR_OP_FILESORT: { - started_objects++; writer->add_member("filesort").start_object(); + if (is_analyze) + ((Explain_aggr_filesort*)node)->tracker->print_json_members(writer); + break; } - writer->add_member("temporary_table").start_object(); - writer->add_member("function").add_str("buffer"); - } - else - { - if (using_filesort) - first_table_sort_used= true; + case AGGR_OP_REMOVE_DUPLICATES: + writer->add_member("duplicate_removal").start_object(); + break; + default: + DBUG_ASSERT(0); } + started_objects++; } - Explain_basic_join::print_explain_json_interns(query, writer, is_analyze, - first_table_sort, - first_table_sort_used); + Explain_basic_join::print_explain_json_interns(query, writer, is_analyze); for (;started_objects; started_objects--) writer->end_object(); @@ -978,7 +904,7 @@ void Explain_basic_join::print_explain_json(Explain_query *query, writer->add_member("query_block").start_object(); writer->add_member("select_id").add_ll(select_id); - print_explain_json_interns(query, writer, is_analyze, NULL, false); + print_explain_json_interns(query, writer, is_analyze); writer->end_object(); } @@ -987,9 +913,7 @@ void Explain_basic_join::print_explain_json(Explain_query *query, void Explain_basic_join:: print_explain_json_interns(Explain_query *query, Json_writer *writer, - bool is_analyze, - Filesort_tracker *first_table_sort, - bool first_table_sort_used) + bool is_analyze) { Json_writer_nesting_guard guard(writer); for (uint i=0; i< n_join_tabs; i++) @@ -997,12 +921,7 @@ print_explain_json_interns(Explain_query *query, if (join_tabs[i]->start_dups_weedout) writer->add_member("duplicates_removal").start_object(); - join_tabs[i]->print_explain_json(query, writer, is_analyze, - first_table_sort, - first_table_sort_used); - - first_table_sort= NULL; - first_table_sort_used= false; + join_tabs[i]->print_explain_json(query, writer, is_analyze); if (join_tabs[i]->end_dups_weedout) writer->end_object(); @@ -1294,7 +1213,7 @@ int Explain_table_access::print_explain(select_result_sink *output, uint8 explai extra_buf.append(STRING_WITH_LEN("Using temporary")); } - if (using_filesort) + if (using_filesort || this->using_filesort) { if (first) first= false; @@ -1493,13 +1412,11 @@ void add_json_keyset(Json_writer *writer, const char *elem_name, void Explain_table_access::print_explain_json(Explain_query *query, Json_writer *writer, - bool is_analyze, - Filesort_tracker *fs_tracker, - bool first_table_sort_used) + bool is_analyze) { Json_writer_nesting_guard guard(writer); - if (first_table_sort_used) + if (using_filesort) { /* filesort was invoked on this join tab before doing the join with the rest */ writer->add_member("read_sorted_file").start_object(); @@ -1526,6 +1443,7 @@ void Explain_table_access::print_explain_json(Explain_query *query, } } writer->add_member("filesort").start_object(); + if (is_analyze) fs_tracker->print_json_members(writer); } @@ -1718,7 +1636,7 @@ void Explain_table_access::print_explain_json(Explain_query *query, writer->end_object(); } - if (first_table_sort_used) + if (using_filesort) { writer->end_object(); // filesort writer->end_object(); // read_sorted_file diff --git a/sql/sql_explain.h b/sql/sql_explain.h index a428dd8befa..3f57f7ac937 100644 --- a/sql/sql_explain.h +++ b/sql/sql_explain.h @@ -176,9 +176,7 @@ public: bool is_analyze); void print_explain_json_interns(Explain_query *query, Json_writer *writer, - bool is_analyze, - Filesort_tracker *first_table_sort, - bool first_table_sort_used); + bool is_analyze); /* A flat array of Explain structs for tables. */ Explain_table_access** join_tabs; @@ -186,6 +184,7 @@ public: }; +class Explain_aggr_node; /* EXPLAIN structure for a SELECT. @@ -212,17 +211,9 @@ public: having(NULL), having_value(Item::COND_UNDEF), using_temporary(false), using_filesort(false), time_tracker(is_analyze), - ops_tracker(is_analyze) + aggr_tree(NULL) {} -#if 0 - /* - This is used to save the results of "late" test_if_skip_sort_order() calls - that are made from JOIN::exec - */ - void replace_table(uint idx, Explain_table_access *new_tab); -#endif - public: const char *select_type; @@ -245,9 +236,13 @@ public: /* ANALYZE members */ Time_and_counter_tracker time_tracker; - - Sort_and_group_tracker ops_tracker; + /* + Part of query plan describing sorting, temp.table usage, and duplicate + removal + */ + Explain_aggr_node* aggr_tree; + int print_explain(Explain_query *query, select_result_sink *output, uint8 explain_flags, bool is_analyze); void print_explain_json(Explain_query *query, Json_writer *writer, @@ -261,6 +256,48 @@ private: Table_access_tracker using_temporary_read_tracker; }; +///////////////////////////////////////////////////////////////////////////// +// EXPLAIN structures for ORDER/GROUP operations. +///////////////////////////////////////////////////////////////////////////// +typedef enum +{ + AGGR_OP_TEMP_TABLE, + AGGR_OP_FILESORT, + //AGGR_OP_READ_SORTED_FILE, // need this? + AGGR_OP_REMOVE_DUPLICATES + //AGGR_OP_JOIN // Need this? +} enum_explain_aggr_node_type; + + +class Explain_aggr_node : public Sql_alloc +{ +public: + virtual enum_explain_aggr_node_type get_type()= 0; + virtual ~Explain_aggr_node() {} + Explain_aggr_node *child; +}; + +class Explain_aggr_filesort : public Explain_aggr_node +{ +public: + enum_explain_aggr_node_type get_type() { return AGGR_OP_FILESORT; } + Filesort_tracker *tracker; +}; + +class Explain_aggr_tmp_table : public Explain_aggr_node +{ +public: + enum_explain_aggr_node_type get_type() { return AGGR_OP_TEMP_TABLE; } +}; + +class Explain_aggr_remove_dups : public Explain_aggr_node +{ +public: + enum_explain_aggr_node_type get_type() { return AGGR_OP_REMOVE_DUPLICATES; } +}; + + +///////////////////////////////////////////////////////////////////////////// /* Explain structure for a UNION. @@ -618,7 +655,9 @@ public: where_cond(NULL), cache_cond(NULL), pushed_index_cond(NULL), - sjm_nest(NULL) + sjm_nest(NULL), + using_filesort(false), + fs_tracker(NULL) {} ~Explain_table_access() { delete sjm_nest; } @@ -711,7 +750,9 @@ public: Item *pushed_index_cond; Explain_basic_join *sjm_nest; - + + bool using_filesort; + Filesort_tracker *fs_tracker; /* ANALYZE members */ /* Tracker for reading the table */ @@ -724,9 +765,7 @@ public: uint select_id, const char *select_type, bool using_temporary, bool using_filesort); void print_explain_json(Explain_query *query, Json_writer *writer, - bool is_analyze, - Filesort_tracker *fs_tracker, - bool first_table_sort_used); + bool is_analyze); private: void append_tag_name(String *str, enum explain_extra_tag tag); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index f2b6956f213..30be455b3d3 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -3110,6 +3110,14 @@ void JOIN::save_explain_data(Explain_query *output, bool can_overwrite, Explain_union *eu= output->get_union(nr); explain= &eu->fake_select_lex_explain; join_tab[0].tracker= eu->get_fake_select_lex_tracker(); + for (int i=0 ; i < top_join_tab_count + aggr_tables; i++) + { + if (join_tab[i].filesort) + { + join_tab[i].filesort->tracker= + new Filesort_tracker(thd->lex->analyze_stmt); + } + } } } @@ -3123,7 +3131,6 @@ void JOIN::exec() dbug_serve_apcs(thd, 1); ); ANALYZE_START_TRACKING(&explain->time_tracker); - explain->ops_tracker.report_join_start(); exec_inner(); ANALYZE_STOP_TRACKING(&explain->time_tracker); @@ -17773,12 +17780,8 @@ do_select(JOIN *join, Procedure *procedure) join->select_lex->select_number)) dbug_serve_apcs(join->thd, 1); ); - JOIN_TAB *join_tab=join->join_tab +join->top_join_tab_count; - for (uint i= 0; i < join->aggr_tables; i++, join_tab++) - { - join->explain->ops_tracker.report_tmp_table(join_tab->table); - } - join_tab= join->join_tab + join->const_tables; + + JOIN_TAB *join_tab= join->join_tab + join->const_tables; if (join->outer_ref_cond && !join->outer_ref_cond->val_int()) error= NESTED_LOOP_NO_MORE_ROWS; else @@ -21260,7 +21263,7 @@ create_sort_index(THD *thd, JOIN *join, JOIN_TAB *tab, Filesort *fsort) table->file->info(HA_STATUS_VARIABLE); // Get record count filesort_retval= filesort(thd, table, fsort, tab->keep_current_rowid, &examined_rows, &found_rows, - join->explain->ops_tracker.report_sorting(thd)); + fsort->tracker); table->sort.found_records= filesort_retval; tab->records= found_rows; // For SQL_CALC_ROWS @@ -21359,7 +21362,7 @@ JOIN_TAB::remove_duplicates() DBUG_ASSERT(join->aggr_tables > 0 && table->s->tmp_table != NO_TMP_TABLE); THD_STAGE_INFO(join->thd, stage_removing_duplicates); - join->explain->ops_tracker.report_duplicate_removal(); + //join->explain->ops_tracker.report_duplicate_removal(); table->reginfo.lock_type=TL_WRITE; @@ -23701,7 +23704,7 @@ int append_possible_keys(MEM_ROOT *alloc, String_list &list, TABLE *table, void JOIN_TAB::save_explain_data(Explain_table_access *eta, table_map prefix_tables, - bool distinct, JOIN_TAB *first_top_tab) + bool distinct_arg, JOIN_TAB *first_top_tab) { int quick_type; CHARSET_INFO *cs= system_charset_info; @@ -23717,6 +23720,8 @@ void JOIN_TAB::save_explain_data(Explain_table_access *eta, explain_plan= eta; eta->key.clear(); eta->quick_info= NULL; + eta->using_filesort= false; + SQL_SELECT *tab_select; /* We assume that if this table does pre-sorting, then it doesn't do filtering @@ -23724,6 +23729,13 @@ void JOIN_TAB::save_explain_data(Explain_table_access *eta, */ DBUG_ASSERT(!(select && filesort)); tab_select= (filesort)? filesort->select : select; + + if (filesort) + { + eta->using_filesort= true; // This fixes EXPLAIN + eta->fs_tracker= filesort->tracker= + new Filesort_tracker(thd->lex->analyze_stmt); + } tracker= &eta->tracker; jbuf_tracker= &eta->jbuf_tracker; @@ -24129,14 +24141,55 @@ void JOIN_TAB::save_explain_data(Explain_table_access *eta, /* + Walk through join->aggr_tables and save aggregation/grouping query plan into + an Explain_select object +*/ + +void save_agg_explain_data(JOIN *join, Explain_select *xpl_sel) +{ + JOIN_TAB *join_tab=join->join_tab + join->top_join_tab_count; + Explain_aggr_node *prev_node; + Explain_aggr_node *node= xpl_sel->aggr_tree; + + for (uint i= 0; i < join->aggr_tables; i++, join_tab++) + { + // Each aggregate means a temp.table + prev_node= node; + node= new Explain_aggr_tmp_table; + node->child= prev_node; + + if (join_tab->distinct) + { + prev_node= node; + node= new Explain_aggr_remove_dups; + node->child= prev_node; + } + + if (join_tab->filesort) + { + Explain_aggr_filesort *eaf = new Explain_aggr_filesort; + eaf->tracker= new Filesort_tracker(join->thd->lex->analyze_stmt); + join_tab->filesort->tracker= eaf->tracker; + + prev_node= node; + node= eaf; + node->child= prev_node; + } + } + xpl_sel->aggr_tree= node; +} + + +/* Save Query Plan Footprint @note Currently, this function may be called multiple times */ -int JOIN::save_explain_data_intern(Explain_query *output, bool need_tmp_table, - bool need_order, bool distinct, +int JOIN::save_explain_data_intern(Explain_query *output, + bool need_tmp_table_arg, + bool need_order_arg, bool distinct_arg, const char *message) { JOIN *join= this; /* Legacy: this code used to be a non-member function */ @@ -24166,7 +24219,7 @@ int JOIN::save_explain_data_intern(Explain_query *output, bool need_tmp_table, explain->select_id= join->select_lex->select_number; explain->select_type= join->select_lex->type; explain->using_temporary= need_tmp; - explain->using_filesort= need_order; + explain->using_filesort= need_order_arg; /* Setting explain->message means that all other members are invalid */ explain->message= message; @@ -24183,7 +24236,7 @@ int JOIN::save_explain_data_intern(Explain_query *output, bool need_tmp_table, explain->select_id= select_lex->select_number; explain->select_type= select_lex->type; explain->using_temporary= need_tmp; - explain->using_filesort= need_order; + explain->using_filesort= need_order_arg; explain->message= "Storage engine handles GROUP BY"; if (select_lex->master_unit()->derived) @@ -24203,43 +24256,8 @@ int JOIN::save_explain_data_intern(Explain_query *output, bool need_tmp_table, xpl_sel->select_type= join->select_lex->type; if (select_lex->master_unit()->derived) xpl_sel->connection_type= Explain_node::EXPLAIN_NODE_DERIVED; - - if (need_tmp_table) - xpl_sel->using_temporary= true; - - if (need_order) - xpl_sel->using_filesort= true; - - /* - Check whether we should display "Using filesort" or "Using temporary". - This is a temporary code, we need to save the 'true' plan structure for - EXPLAIN FORMAT=JSON. - */ - { - bool using_filesort_= false; - bool using_temporary_ = false; - /* The first non-const join table may do sorting */ - JOIN_TAB *tab= first_top_level_tab(this, WITHOUT_CONST_TABLES); - if (tab) - { - if (tab->filesort) - using_filesort_= true; - if (tab->aggr) - using_temporary_= true; - } - - /* Aggregation tabs are located at the end of top-level join tab array. */ - JOIN_TAB *curr_tab= join_tab + top_join_tab_count; - for (uint i= 0; i < aggr_tables; i++, curr_tab++) - { - if (curr_tab->filesort) - using_filesort_= true; - if (curr_tab->aggr) - using_temporary_= true; - } - xpl_sel->using_temporary= using_temporary_; - xpl_sel->using_filesort= using_filesort_; - } + + save_agg_explain_data(this, xpl_sel); xpl_sel->exec_const_cond= exec_const_cond; if (tmp_having) @@ -24297,7 +24315,7 @@ int JOIN::save_explain_data_intern(Explain_query *output, bool need_tmp_table, prev_bush_root_tab= tab->bush_root_tab; cur_parent->add_table(eta, output); - tab->save_explain_data(eta, used_tables, distinct, first_top_tab); + tab->save_explain_data(eta, used_tables, distinct_arg, first_top_tab); if (saved_join_tab) tab= saved_join_tab; diff --git a/sql/sql_window.cc b/sql/sql_window.cc index 6284fd2ffed..0352a0dfb5f 100644 --- a/sql/sql_window.cc +++ b/sql/sql_window.cc @@ -1484,6 +1484,7 @@ bool Window_func_runner::setup(THD *thd) spec->partition_list->first, spec->order_list->first); filesort= new (thd->mem_root) Filesort(sort_order, HA_POS_ERROR, NULL); + filesort->tracker= new Filesort_tracker(thd->lex->analyze_stmt); win_func->setup_partition_border_check(thd); |