diff options
-rw-r--r-- | mysql-test/r/analyze_stmt.result | 199 | ||||
-rw-r--r-- | mysql-test/r/ps.result | 1 | ||||
-rw-r--r-- | mysql-test/r/show_explain.result | 2 | ||||
-rw-r--r-- | mysql-test/t/analyze_stmt.test | 148 | ||||
-rw-r--r-- | mysql-test/t/ps.test | 1 | ||||
-rw-r--r-- | sql/protocol.h | 36 | ||||
-rw-r--r-- | sql/sql_class.cc | 19 | ||||
-rw-r--r-- | sql/sql_class.h | 14 | ||||
-rw-r--r-- | sql/sql_delete.cc | 11 | ||||
-rw-r--r-- | sql/sql_explain.cc | 133 | ||||
-rw-r--r-- | sql/sql_explain.h | 106 | ||||
-rw-r--r-- | sql/sql_join_cache.cc | 38 | ||||
-rw-r--r-- | sql/sql_join_cache.h | 8 | ||||
-rw-r--r-- | sql/sql_lex.cc | 5 | ||||
-rw-r--r-- | sql/sql_lex.h | 4 | ||||
-rw-r--r-- | sql/sql_parse.cc | 38 | ||||
-rw-r--r-- | sql/sql_select.cc | 920 | ||||
-rw-r--r-- | sql/sql_select.h | 23 | ||||
-rw-r--r-- | sql/sql_show.cc | 229 | ||||
-rw-r--r-- | sql/sql_show.h | 63 | ||||
-rw-r--r-- | sql/sql_update.cc | 26 | ||||
-rw-r--r-- | sql/sql_yacc.yy | 11 | ||||
-rw-r--r-- | sql/table.h | 16 |
23 files changed, 1473 insertions, 578 deletions
diff --git a/mysql-test/r/analyze_stmt.result b/mysql-test/r/analyze_stmt.result new file mode 100644 index 00000000000..41fdce43807 --- /dev/null +++ b/mysql-test/r/analyze_stmt.result @@ -0,0 +1,199 @@ +drop table if exists t0,t1,t2,t3; +create table t0 (a int) engine=myisam; +INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int) engine=myisam; +INSERT INTO t1 select * from t0; +# Try a few basic selects to see that r_rows and r_filtered columns work +analyze select * from t1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 10 100.00 100.00 +analyze select * from t1 where a<5; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 10 100.00 50.00 Using where +analyze select * from t1 where a>100; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 10 100.00 0.00 Using where +# ANALYZE DELETE will delete rows: +analyze delete from t1 where a in (2,3,4); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 NULL 100.00 30.00 Using where +select * from t1; +a +0 +1 +5 +6 +7 +8 +9 +drop table t1; +# ANALYZE UPDATE will make updates: +create table t1(a int, b int); +insert into t1 select a,a from t0; +analyze update t1 set b=100+b where a in (6,7,8); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 NULL 100.00 30.00 Using where +select * from t1; +a b +0 0 +1 1 +2 2 +3 3 +4 4 +5 5 +6 106 +7 107 +8 108 +9 9 +drop table t1; +# Check that UNION works +create table t1(a int, b int); +insert into t1 select a,a from t0; +analyze (select * from t1 A where a<5) union (select * from t1 B where a in (5,6)); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY A ALL NULL NULL NULL NULL 10 10 100.00 50.00 Using where +2 UNION B ALL NULL NULL NULL NULL 10 10 100.00 20.00 Using where +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 7 NULL NULL +analyze (select * from t1 A where a<5) union (select * from t1 B where a in (1,2)); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY A ALL NULL NULL NULL NULL 10 10 100.00 50.00 Using where +2 UNION B ALL NULL NULL NULL NULL 10 10 100.00 20.00 Using where +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 5 NULL NULL +drop table t1; +drop table t0; +# +# Try a subquery. +# +create table t0 (a int, b int); +insert into t0 values +(0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); +create table t1 (a int, b int); +insert into t1 values (1,1),(2,2),(3,3); +# See .test file for the right values of r_rows and r_filtered. +analyze select a, a in (select t0.b from t0 where t0.b+1=t1.b+1) from t1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 3 100.00 100.00 +2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 10 3 100.00 33.33 Using where +# Try a subquery that is never executed +analyze select a, a in (select t0.b from t0 where t0.b+1=t1.b+1) from t1 where t1.a > 5; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 3 100.00 0.00 Using where +2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 10 NULL 100.00 NULL Using where +drop table t0, t1; +# +# Tests for join buffering +# +create table t0 (a int, b int); +insert into t0 values +(0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); +create table t1 like t0; +insert into t1 select * from t0; +explain select * from t0, t1 where t0.a<5 and t1.a<5; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +# These should have filtered=50 +analyze select * from t0, t1 where t0.a<5 and t1.a<5; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 10 100.00 50.00 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 10 100.00 50.00 Using where; Using join buffer (flat, BNL join) +explain select * from t0, t1 where t0.a<5 and t1.b=t0.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +# Now, t1 should have filtered=10 +analyze select * from t0, t1 where t0.a<5 and t1.b=t0.b; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 10 100.00 50.00 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 10 100.00 10.00 Using where; Using join buffer (flat, BNL join) +explain select * from t0, t1 where t0.a<5 and t1.a<5 and t1.b=t0.b; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join) +# Now, t1 should have filtered=10 +analyze select * from t0, t1 where t0.a<5 and t1.a<5 and t1.b=t0.b; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 10 100.00 50.00 Using where +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 10 100.00 10.00 Using where; Using join buffer (flat, BNL join) +# TODO: Check what is counted for "range checked for each record". +# +# Test for joins +# +create table t2 (key1 int, key2x int, col1 int, key(key1), key(key2x)); +insert into t2 select A.a + 10 *B.a +100 * C.a, +(A.a + 10 *B.a +100 * C.a)*2, +A.a + 10 *B.a +100 * C.a +from t0 A, t0 B, t0 C; +# This always has matches, filtered=100%. +analyze select * from t1,t2 where t2.key1=t1.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 10 100.00 100.00 Using where +1 SIMPLE t2 ref key1 key1 5 test.t1.a 1 1 100.00 100.00 +# This shows r_rows=0. It is actually 0.5 (should r_rows be changed to double?) +analyze select * from t1,t2 where t2.key2x=t1.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 10 100.00 100.00 Using where +1 SIMPLE t2 ref key2x key2x 5 test.t1.a 1 0 100.00 100.00 +select * from t1,t2 where t2.key2x=t1.a; +a b key1 key2x col1 +0 0 0 0 0 +2 2 1 2 1 +4 4 2 4 2 +6 6 3 6 3 +8 8 4 8 4 +# This has t2.filtered=40% (there are 5 values: {0,1,2,3,4}. two of them have mod=0) +analyze select * from t1,t2 where t2.key2x=t1.a and mod(t2.col1,4)=0; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 10 10 100.00 100.00 Using where +1 SIMPLE t2 ref key2x key2x 5 test.t1.a 1 0 100.00 40.00 Using where +drop table t0,t1,t2; +# +# Check non-merged derived tables +# +create table t0 (a int, b int); +insert into t0 values +(0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); +update t0 set b=b/3; +analyze select * from (select count(*),max(a),b from t0 group by b) T; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 10 4 100.00 100.00 +2 DERIVED t0 ALL NULL NULL NULL NULL 10 10 100.00 100.00 Using temporary; Using filesort +drop table t0; +# +# Check ORDER/GROUP BY +# +create table t0 (a int, b int); +insert into t0 values +(0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); +analyze select count(*),max(a),b from t0 where a<7 group by b; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t0 ALL NULL NULL NULL NULL 10 10 100.00 70.00 Using where; Using temporary; Using filesort +drop table t0; +# +# Check multi-table UPDATE/DELETE. +# +create table t0 (a int, b int); +create table t1 (a int, b int); +insert into t0 values (0,0),(2,2),(4,4), (8,8); +insert into t1 values (0,0),(2,2), (6,6); +analyze select * from t0,t1 where t0.a=t1.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 3 100.00 100.00 +1 SIMPLE t0 ALL NULL NULL NULL NULL 4 4 100.00 16.67 Using where; Using join buffer (flat, BNL join) +analyze update t0,t1 set t1.b=5555 where t0.a=t1.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 3 100.00 100.00 +1 SIMPLE t0 ALL NULL NULL NULL NULL 4 4 100.00 16.67 Using where +select * from t1; +a b +0 5555 +2 5555 +6 6 +analyze delete t1 from t1, t0 where t0.a=t1.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 3 100.00 100.00 +1 SIMPLE t0 ALL NULL NULL NULL NULL 4 4 100.00 16.67 Using where +select * from t1; +a b +6 6 +drop table t0, t1; diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result index 31fcea528aa..3217a10ed6d 100644 --- a/mysql-test/r/ps.result +++ b/mysql-test/r/ps.result @@ -1,5 +1,6 @@ call mtr.add_suppression('Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.'); drop table if exists t1,t2,t3,t4; +drop database if exists mysqltest1; drop database if exists client_test_db; create table t1 ( diff --git a/mysql-test/r/show_explain.result b/mysql-test/r/show_explain.result index a4d12ce05ce..3695384bac4 100644 --- a/mysql-test/r/show_explain.result +++ b/mysql-test/r/show_explain.result @@ -641,7 +641,7 @@ set debug_dbug='+d,show_explain_probe_join_exec_start'; SHOW INDEX FROM t1; show explain for $thr2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE STATISTICS ALL NULL NULL NULL NULL NULL Skip_open_table; Scanned all databases +1 SIMPLE STATISTICS ALL NULL TABLE_SCHEMA,TABLE_NAME NULL NULL NULL Open_full_table; Scanned 0 databases Warnings: Note 1003 SHOW INDEX FROM t1 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment diff --git a/mysql-test/t/analyze_stmt.test b/mysql-test/t/analyze_stmt.test new file mode 100644 index 00000000000..2f9de4a3763 --- /dev/null +++ b/mysql-test/t/analyze_stmt.test @@ -0,0 +1,148 @@ +# +# Tests for "ANALYZE $statement" feature (PostgreSQL's analog is called EXPLAIN ANALYZE) +# +--disable_warnings +drop table if exists t0,t1,t2,t3; +--enable_warnings + +create table t0 (a int) engine=myisam; +INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t1 (a int) engine=myisam; +INSERT INTO t1 select * from t0; + +--echo # Try a few basic selects to see that r_rows and r_filtered columns work +analyze select * from t1; +analyze select * from t1 where a<5; +analyze select * from t1 where a>100; + +--echo # ANALYZE DELETE will delete rows: +analyze delete from t1 where a in (2,3,4); +select * from t1; +drop table t1; + +--echo # ANALYZE UPDATE will make updates: +create table t1(a int, b int); +insert into t1 select a,a from t0; +analyze update t1 set b=100+b where a in (6,7,8); +select * from t1; +drop table t1; + +--echo # Check that UNION works +create table t1(a int, b int); +insert into t1 select a,a from t0; +analyze (select * from t1 A where a<5) union (select * from t1 B where a in (5,6)); +analyze (select * from t1 A where a<5) union (select * from t1 B where a in (1,2)); +drop table t1; +drop table t0; + +--echo # +--echo # Try a subquery. +--echo # +create table t0 (a int, b int); +insert into t0 values + (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); + +create table t1 (a int, b int); +insert into t1 values (1,1),(2,2),(3,3); + +# +# t1 t0 +# a=1 (0,1) 2 rows +# a=2 (0,1,2) 3 rows +# a=3 (0,1,2,3) 4 rows +# +# TOTAL TOTAL= 9 rows. 3 executions, avg=3 rows. +# WHERE is satisfied for 1 row per query, which gives filtered=33.3 + +--echo # See .test file for the right values of r_rows and r_filtered. +analyze select a, a in (select t0.b from t0 where t0.b+1=t1.b+1) from t1; + +--echo # Try a subquery that is never executed +analyze select a, a in (select t0.b from t0 where t0.b+1=t1.b+1) from t1 where t1.a > 5; + +drop table t0, t1; + +--echo # +--echo # Tests for join buffering +--echo # +create table t0 (a int, b int); +insert into t0 values + (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); +create table t1 like t0; +insert into t1 select * from t0; + +explain select * from t0, t1 where t0.a<5 and t1.a<5; +--echo # These should have filtered=50 +analyze select * from t0, t1 where t0.a<5 and t1.a<5; + +explain select * from t0, t1 where t0.a<5 and t1.b=t0.b; +--echo # Now, t1 should have filtered=10 +analyze select * from t0, t1 where t0.a<5 and t1.b=t0.b; + +explain select * from t0, t1 where t0.a<5 and t1.a<5 and t1.b=t0.b; +--echo # Now, t1 should have filtered=10 +analyze select * from t0, t1 where t0.a<5 and t1.a<5 and t1.b=t0.b; + +--echo # TODO: Check what is counted for "range checked for each record". + +--echo # +--echo # Test for joins +--echo # +create table t2 (key1 int, key2x int, col1 int, key(key1), key(key2x)); +insert into t2 select A.a + 10 *B.a +100 * C.a, + (A.a + 10 *B.a +100 * C.a)*2, + A.a + 10 *B.a +100 * C.a + from t0 A, t0 B, t0 C; + +--echo # This always has matches, filtered=100%. +analyze select * from t1,t2 where t2.key1=t1.a; + +--echo # This shows r_rows=0. It is actually 0.5 (should r_rows be changed to double?) +analyze select * from t1,t2 where t2.key2x=t1.a; + select * from t1,t2 where t2.key2x=t1.a; + +--echo # This has t2.filtered=40% (there are 5 values: {0,1,2,3,4}. two of them have mod=0) +analyze select * from t1,t2 where t2.key2x=t1.a and mod(t2.col1,4)=0; + +drop table t0,t1,t2; + +--echo # +--echo # Check non-merged derived tables +--echo # +create table t0 (a int, b int); +insert into t0 values + (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); + +update t0 set b=b/3; +analyze select * from (select count(*),max(a),b from t0 group by b) T; +drop table t0; + +--echo # +--echo # Check ORDER/GROUP BY +--echo # +create table t0 (a int, b int); +insert into t0 values + (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9); + +analyze select count(*),max(a),b from t0 where a<7 group by b; +drop table t0; + +--echo # +--echo # Check multi-table UPDATE/DELETE. +--echo # +create table t0 (a int, b int); +create table t1 (a int, b int); +insert into t0 values (0,0),(2,2),(4,4), (8,8); +insert into t1 values (0,0),(2,2), (6,6); + +analyze select * from t0,t1 where t0.a=t1.a; + +analyze update t0,t1 set t1.b=5555 where t0.a=t1.a; +select * from t1; + +analyze delete t1 from t1, t0 where t0.a=t1.a; +select * from t1; + +drop table t0, t1; + diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test index 0233b2e428b..491594a3045 100644 --- a/mysql-test/t/ps.test +++ b/mysql-test/t/ps.test @@ -7,6 +7,7 @@ call mtr.add_suppression('Unsafe statement written to the binary log using state --disable_warnings drop table if exists t1,t2,t3,t4; +drop database if exists mysqltest1; # Avoid wrong warnings if mysql_client_test fails drop database if exists client_test_db; --enable_warnings diff --git a/sql/protocol.h b/sql/protocol.h index c58de68289f..5129f68d706 100644 --- a/sql/protocol.h +++ b/sql/protocol.h @@ -210,6 +210,42 @@ public: virtual enum enum_protocol_type type() { return PROTOCOL_BINARY; }; }; + +/* + A helper for "ANALYZE $stmt" which looks a real network procotol but doesn't + write results to the network. + + At first glance, class select_send looks like a more appropriate place to + implement the "write nothing" hook. This is not true, because + - we need to evaluate the value of every item, and do it the way + select_send does it (i.e. call item->val_int() or val_real() or...) + - select_send::send_data() has some other code, like telling the storage + engine that the row can be unlocked. We want to keep that also. + as a result, "ANALYZE $stmt" uses a select_send_analyze which still uses + select_send::send_data() & co., and also uses Protocol_discard object. +*/ + +class Protocol_discard : public Protocol_text +{ +public: + Protocol_discard(THD *thd_arg) : Protocol_text(thd_arg) {} + /* The real writing is done only in write() */ + virtual bool write() { return 0; } + virtual bool send_result_set_metadata(List<Item> *list, uint flags) + { + // Don't pas Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF flags + return Protocol_text::send_result_set_metadata(list, 0); + } + + // send_error is intentionally not overloaded. + virtual bool send_eof(uint server_status, uint statement_warn_count) + { + return 0; + } + +}; + + void send_warning(THD *thd, uint sql_errno, const char *err=0); bool net_send_error(THD *thd, uint sql_errno, const char *err, const char* sqlstate); diff --git a/sql/sql_class.cc b/sql/sql_class.cc index a48ebd450bb..8d6ddc0bb08 100644 --- a/sql/sql_class.cc +++ b/sql/sql_class.cc @@ -2282,6 +2282,9 @@ int THD::send_explain_fields(select_result *result) /* Populate the provided field_list with EXPLAIN output columns. this->lex->describe has the EXPLAIN flags + + The set/order of columns must be kept in sync with + Explain_query::print_explain and co. */ void THD::make_explain_field_list(List<Item> &field_list) @@ -2317,11 +2320,25 @@ void THD::make_explain_field_list(List<Item> &field_list) item->maybe_null=1; field_list.push_back(item= new Item_return_int("rows", 10, MYSQL_TYPE_LONGLONG)); - if (lex->describe & DESCRIBE_EXTENDED) + if (lex->analyze_stmt) + { + field_list.push_back(item= new Item_return_int("r_rows", 10, + MYSQL_TYPE_LONGLONG)); + item->maybe_null=1; + } + + if (lex->analyze_stmt || lex->describe & DESCRIBE_EXTENDED) { field_list.push_back(item= new Item_float("filtered", 0.1234, 2, 4)); item->maybe_null=1; } + + if (lex->analyze_stmt) + { + field_list.push_back(item= new Item_float("r_filtered", 0.1234, 2, 4)); + item->maybe_null=1; + } + item->maybe_null= 1; field_list.push_back(new Item_empty_string("Extra", 255, cs)); } diff --git a/sql/sql_class.h b/sql/sql_class.h index 09b92e454de..f3537086132 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -3961,6 +3961,20 @@ public: }; +/* + We need this class, because select_send::send_eof() will call ::my_eof. + + See also class Protocol_discard. +*/ + +class select_send_analyze : public select_send +{ + bool send_result_set_metadata(List<Item> &list, uint flags) { return 0; } + bool send_eof() { return 0; } + void abort_result_set() {} +}; + + class select_to_file :public select_result_interceptor { protected: sql_exchange *exchange; diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index b3a8eb2a97b..8e776d7281c 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -223,6 +223,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, killed_state killed_status= NOT_KILLED; THD::enum_binlog_query_type query_type= THD::ROW_QUERY_TYPE; bool with_select= !select_lex->item_list.is_empty(); + Explain_delete *explain; Delete_plan query_plan(thd->mem_root); query_plan.index= MAX_KEY; query_plan.using_filesort= FALSE; @@ -538,9 +539,11 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, goto cleanup; } + explain= (Explain_delete*)thd->lex->explain->get_upd_del_plan(); while (!(error=info.read_record(&info)) && !thd->killed && ! thd->is_error()) { + explain->on_record_read(); if (table->vfield) update_virtual_fields(thd, table, table->triggers ? VCOL_UPDATE_ALL : @@ -549,6 +552,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, // thd->is_error() is tested to disallow delete row on error if (!select || select->skip_record(thd) > 0) { + explain->on_record_after_where(); if (table->triggers && table->triggers->process_triggers(thd, TRG_EVENT_DELETE, TRG_ACTION_BEFORE, FALSE)) @@ -666,6 +670,11 @@ cleanup: } DBUG_ASSERT(transactional_table || !deleted || thd->transaction.stmt.modified_non_trans_table); free_underlaid_joins(thd, select_lex); + if (thd->lex->analyze_stmt) + { + error= thd->lex->explain->send_explain(thd); + } + else if (error < 0 || (thd->lex->ignore && !thd->is_error() && !thd->is_fatal_error)) { @@ -1283,7 +1292,7 @@ bool multi_delete::send_eof() if (local_error != 0) error_handled= TRUE; // to force early leave from ::abort_result_set() - if (!local_error) + if (!local_error && !thd->lex->analyze_stmt) { ::my_ok(thd, deleted); } diff --git a/sql/sql_explain.cc b/sql/sql_explain.cc index 84ff8759d96..af51c5fc382 100644 --- a/sql/sql_explain.cc +++ b/sql/sql_explain.cc @@ -140,7 +140,7 @@ int Explain_query::send_explain(THD *thd) return 1; int res; - if ((res= print_explain(result, lex->describe))) + if ((res= print_explain(result, lex->describe, lex->analyze_stmt))) result->abort_result_set(); else result->send_eof(); @@ -154,16 +154,16 @@ int Explain_query::send_explain(THD *thd) */ int Explain_query::print_explain(select_result_sink *output, - uint8 explain_flags) + uint8 explain_flags, bool is_analyze) { if (upd_del_plan) { - upd_del_plan->print_explain(this, output, explain_flags); + upd_del_plan->print_explain(this, output, explain_flags, is_analyze); return 0; } else if (insert_plan) { - insert_plan->print_explain(this, output, explain_flags); + insert_plan->print_explain(this, output, explain_flags, is_analyze); return 0; } else @@ -172,14 +172,14 @@ int Explain_query::print_explain(select_result_sink *output, Explain_node *node= get_node(1); if (!node) return 1; /* No query plan */ - return node->print_explain(this, output, explain_flags); + return node->print_explain(this, output, explain_flags, is_analyze); } } bool print_explain_query(LEX *lex, THD *thd, String *str) { - return lex->explain->print_explain_str(thd, str); + return lex->explain->print_explain_str(thd, str, false); } @@ -187,14 +187,14 @@ bool print_explain_query(LEX *lex, THD *thd, String *str) Return tabular EXPLAIN output as a text string */ -bool Explain_query::print_explain_str(THD *thd, String *out_str) +bool Explain_query::print_explain_str(THD *thd, String *out_str, bool is_analyze) { List<Item> fields; thd->make_explain_field_list(fields); select_result_text_buffer output_buf(thd); output_buf.send_result_set_metadata(fields, thd->lex->describe); - if (print_explain(&output_buf, thd->lex->describe)) + if (print_explain(&output_buf, thd->lex->describe, is_analyze)) return true; output_buf.save_to(out_str); return false; @@ -217,7 +217,8 @@ static void push_string(List<Item> *item_list, String *str) int Explain_union::print_explain(Explain_query *query, select_result_sink *output, - uint8 explain_flags) + uint8 explain_flags, + bool is_analyze) { char table_name_buffer[SAFE_NAME_LEN]; @@ -225,7 +226,7 @@ int Explain_union::print_explain(Explain_query *query, for (int i= 0; i < (int) union_members.elements(); i++) { Explain_select *sel= query->get_select(union_members.at(i)); - sel->print_explain(query, output, explain_flags); + sel->print_explain(query, output, explain_flags, is_analyze); } /* Print a line with "UNION RESULT" */ @@ -287,9 +288,21 @@ int Explain_union::print_explain(Explain_query *query, /* `rows` */ item_list.push_back(item_null); + + /* `r_rows` */ + if (is_analyze) + { + ha_rows avg_rows= fake_select_lex_tracker.get_avg_rows(); + item_list.push_back(new Item_int((longlong) (ulonglong) avg_rows, + MY_INT64_NUM_DECIMAL_DIGITS)); + } /* `filtered` */ - if (explain_flags & DESCRIBE_EXTENDED) + if (explain_flags & DESCRIBE_EXTENDED || is_analyze) + item_list.push_back(item_null); + + /* `r_filtered` */ + if (is_analyze) item_list.push_back(item_null); /* `Extra` */ @@ -309,7 +322,7 @@ int Explain_union::print_explain(Explain_query *query, Print all subquery children (UNION children have already been printed at the start of this function) */ - return print_explain_for_children(query, output, explain_flags); + return print_explain_for_children(query, output, explain_flags, is_analyze); } @@ -319,18 +332,26 @@ int Explain_union::print_explain(Explain_query *query, int Explain_node::print_explain_for_children(Explain_query *query, select_result_sink *output, - uint8 explain_flags) + uint8 explain_flags, + bool is_analyze) { for (int i= 0; i < (int) children.elements(); i++) { Explain_node *node= query->get_node(children.at(i)); - if (node->print_explain(query, output, explain_flags)) + if (node->print_explain(query, output, explain_flags, is_analyze)) return 1; } return 0; } +void Explain_select::replace_table(uint idx, Explain_table_access *new_tab) +{ + delete join_tabs[idx]; + join_tabs[idx]= new_tab; +} + + Explain_select::~Explain_select() { if (join_tabs) @@ -344,7 +365,7 @@ Explain_select::~Explain_select() int Explain_select::print_explain(Explain_query *query, select_result_sink *output, - uint8 explain_flags) + uint8 explain_flags, bool is_analyze) { if (message) { @@ -359,8 +380,17 @@ int Explain_select::print_explain(Explain_query *query, item_list.push_back(item_null); if (explain_flags & DESCRIBE_PARTITIONS) item_list.push_back(item_null); - if (explain_flags & DESCRIBE_EXTENDED) + + /* filtered */ + if (is_analyze || explain_flags & DESCRIBE_EXTENDED) + item_list.push_back(item_null); + + if (is_analyze) + { + /* r_rows, r_filtered */ + item_list.push_back(item_null); item_list.push_back(item_null); + } item_list.push_back(new Item_string(message,strlen(message),cs)); @@ -373,7 +403,7 @@ int Explain_select::print_explain(Explain_query *query, bool using_fs= using_filesort; for (uint i=0; i< n_join_tabs; i++) { - join_tabs[i]->print_explain(output, explain_flags, select_id, + join_tabs[i]->print_explain(output, explain_flags, is_analyze, select_id, select_type, using_tmp, using_fs); if (i == 0) { @@ -387,7 +417,7 @@ int Explain_select::print_explain(Explain_query *query, } } - return print_explain_for_children(query, output, explain_flags); + return print_explain_for_children(query, output, explain_flags, is_analyze); } @@ -398,8 +428,9 @@ void Explain_table_access::push_extra(enum explain_extra_tag extra_tag) int Explain_table_access::print_explain(select_result_sink *output, uint8 explain_flags, - uint select_id, const char *select_type, - bool using_temporary, bool using_filesort) + bool is_analyze, + uint select_id, const char *select_type, + bool using_temporary, bool using_filesort) { const CHARSET_INFO *cs= system_charset_info; const char *hash_key_prefix= "#hash#"; @@ -519,8 +550,23 @@ int Explain_table_access::print_explain(select_result_sink *output, uint8 explai else item_list.push_back(item_null); + /* `r_rows` */ + if (is_analyze) + { + if (!tracker.has_scans()) + { + item_list.push_back(item_null); + } + else + { + ha_rows avg_rows= tracker.get_avg_rows(); + item_list.push_back(new Item_int((longlong) (ulonglong) avg_rows, + MY_INT64_NUM_DECIMAL_DIGITS)); + } + } + /* `filtered` */ - if (explain_flags & DESCRIBE_EXTENDED) + if (explain_flags & DESCRIBE_EXTENDED || is_analyze) { if (filtered_set) { @@ -530,6 +576,22 @@ int Explain_table_access::print_explain(select_result_sink *output, uint8 explai item_list.push_back(item_null); } + /* `r_filtered` */ + if (is_analyze) + { + if (!tracker.has_scans()) + { + item_list.push_back(item_null); + } + else + { + double r_filtered= tracker.get_filtered_after_where(); + if (bka_type.is_using_jbuf()) + r_filtered *= jbuf_tracker.get_filtered_after_where(); + item_list.push_back(new Item_float(r_filtered*100.0, 2)); + } + } + /* `Extra` */ StringBuffer<256> extra_buf; bool first= true; @@ -802,12 +864,13 @@ void Explain_quick_select::print_key_len(String *str) int Explain_delete::print_explain(Explain_query *query, select_result_sink *output, - uint8 explain_flags) + uint8 explain_flags, + bool is_analyze) { if (deleting_all_rows) { const char *msg= "Deleting all rows"; - int res= print_explain_message_line(output, explain_flags, + int res= print_explain_message_line(output, explain_flags, is_analyze, 1 /*select number*/, select_type, &rows, msg); return res; @@ -815,14 +878,16 @@ int Explain_delete::print_explain(Explain_query *query, } else { - return Explain_update::print_explain(query, output, explain_flags); + return Explain_update::print_explain(query, output, explain_flags, + is_analyze); } } int Explain_update::print_explain(Explain_query *query, select_result_sink *output, - uint8 explain_flags) + uint8 explain_flags, + bool is_analyze) { StringBuffer<64> key_buf; StringBuffer<64> key_len_buf; @@ -832,7 +897,7 @@ int Explain_update::print_explain(Explain_query *query, const char *msg= impossible_where ? "Impossible WHERE" : "No matching rows after partition pruning"; - int res= print_explain_message_line(output, explain_flags, + int res= print_explain_message_line(output, explain_flags, is_analyze, 1 /*select number*/, select_type, NULL, /* rows */ @@ -892,8 +957,9 @@ int Explain_update::print_explain(Explain_query *query, Single-table DELETE commands do not do "Using temporary". "Using index condition" is also not possible (which is an unjustified limitation) */ + double r_filtered= 100 * (r_rows?((double)r_rows_after_where/r_rows):1.0); - print_explain_row(output, explain_flags, + print_explain_row(output, explain_flags, is_analyze, 1, /* id */ select_type, table_name.c_ptr(), @@ -904,18 +970,21 @@ int Explain_update::print_explain(Explain_query *query, key_len_buf.length() ? key_len_buf.c_ptr() : NULL, NULL, /* 'ref' is always NULL in single-table EXPLAIN DELETE */ &rows, + &r_rows, + r_filtered, extra_str.c_ptr_safe()); - return print_explain_for_children(query, output, explain_flags); + return print_explain_for_children(query, output, explain_flags, is_analyze); } int Explain_insert::print_explain(Explain_query *query, select_result_sink *output, - uint8 explain_flags) + uint8 explain_flags, + bool is_analyze) { const char *select_type="INSERT"; - print_explain_row(output, explain_flags, + print_explain_row(output, explain_flags, is_analyze, 1, /* id */ select_type, table_name.c_ptr(), @@ -926,9 +995,11 @@ int Explain_insert::print_explain(Explain_query *query, NULL, // key_len NULL, // ref NULL, // rows + NULL, // r_rows + 100.0, // r_filtered NULL); - return print_explain_for_children(query, output, explain_flags); + return print_explain_for_children(query, output, explain_flags, is_analyze); } diff --git a/sql/sql_explain.h b/sql/sql_explain.h index b9f381b867b..a36f1676c57 100644 --- a/sql/sql_explain.h +++ b/sql/sql_explain.h @@ -14,6 +14,38 @@ along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */ +/* Data structures for ANALYZE */ +class Table_access_tracker +{ +public: + Table_access_tracker() : + r_scans(0), r_rows(0), /*r_rows_after_table_cond(0),*/ + r_rows_after_where(0) + {} + + ha_rows r_scans; /* How many scans were ran on this join_tab */ + ha_rows r_rows; /* How many rows we've got after that */ +// ha_rows r_rows_after_table_cond; /* Rows after applying the table condition */ + ha_rows r_rows_after_where; /* Rows after applying attached part of WHERE */ + + bool has_scans() { return (r_scans != 0); } + ha_rows get_avg_rows() + { + return r_scans ? (ha_rows)rint((double) r_rows / r_scans): 0; + } + + double get_filtered_after_where() + { + double r_filtered; + if (r_rows > 0) + r_filtered= (double)r_rows_after_where / r_rows; + else + r_filtered= 1.0; + + return r_filtered; + } +}; + /************************************************************************************** @@ -60,10 +92,10 @@ public: } virtual int print_explain(Explain_query *query, select_result_sink *output, - uint8 explain_flags)=0; + uint8 explain_flags, bool is_analyze)=0; int print_explain_for_children(Explain_query *query, select_result_sink *output, - uint8 explain_flags); + uint8 explain_flags, bool is_analyze); virtual ~Explain_node(){} }; @@ -109,6 +141,12 @@ public: join_tabs[n_join_tabs++]= tab; return false; } + + /* + 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); public: int select_id; @@ -134,7 +172,14 @@ public: bool using_filesort; int print_explain(Explain_query *query, select_result_sink *output, - uint8 explain_flags); + uint8 explain_flags, bool is_analyze); + + Table_access_tracker *get_using_temporary_read_tracker() + { + return &using_temporary_read_tracker; + } +private: + Table_access_tracker using_temporary_read_tracker; }; @@ -172,10 +217,23 @@ public: union_members.append(select_no); } int print_explain(Explain_query *query, select_result_sink *output, - uint8 explain_flags); + uint8 explain_flags, bool is_analyze); const char *fake_select_type; bool using_filesort; + + Table_access_tracker *get_fake_select_lex_tracker() + { + return &fake_select_lex_tracker; + } + Table_access_tracker *get_tmptable_read_tracker() + { + return &tmptable_read_tracker; + } +private: + Table_access_tracker fake_select_lex_tracker; + /* This one is for reading after ORDER BY */ + Table_access_tracker tmptable_read_tracker; }; @@ -183,6 +241,7 @@ class Explain_update; class Explain_delete; class Explain_insert; + /* Explain structure for a query (i.e. a statement). @@ -238,13 +297,14 @@ public: Explain_union *get_union(uint select_id); /* Produce a tabular EXPLAIN output */ - int print_explain(select_result_sink *output, uint8 explain_flags); + int print_explain(select_result_sink *output, uint8 explain_flags, + bool is_analyze); /* Send tabular EXPLAIN to the client */ int send_explain(THD *thd); /* Return tabular EXPLAIN output as a text string */ - bool print_explain_str(THD *thd, String *out_str); + bool print_explain_str(THD *thd, String *out_str, bool is_analyze); /* If true, at least part of EXPLAIN can be printed */ bool have_query_plan() { return insert_plan || upd_del_plan|| get_node(1) != NULL; } @@ -252,6 +312,8 @@ public: void query_plan_ready(); MEM_ROOT *mem_root; + + Explain_update *get_upd_del_plan() { return upd_del_plan; } private: /* Explain_delete inherits from Explain_update */ Explain_update *upd_del_plan; @@ -320,13 +382,17 @@ enum explain_extra_tag }; -typedef struct st_explain_bka_type +class EXPLAIN_BKA_TYPE { +public: + EXPLAIN_BKA_TYPE() : join_alg(NULL) {} + bool incremental; const char *join_alg; StringBuffer<64> mrr_type; - -} EXPLAIN_BKA_TYPE; + + bool is_using_jbuf() { return (join_alg != NULL); } +}; /* @@ -386,6 +452,7 @@ private: /* EXPLAIN data structure for a single JOIN_TAB. */ + class Explain_table_access : public Sql_alloc { public: @@ -459,8 +526,14 @@ public: StringBuffer<32> firstmatch_table_name; int print_explain(select_result_sink *output, uint8 explain_flags, + bool is_analyze, uint select_id, const char *select_type, bool using_temporary, bool using_filesort); + + /* ANALYZE members*/ + Table_access_tracker tracker; + Table_access_tracker jbuf_tracker; + private: void append_tag_name(String *str, enum explain_extra_tag tag); }; @@ -502,8 +575,17 @@ public: bool using_filesort; bool using_io_buffer; + /* ANALYZE members and methods */ + ha_rows r_rows; + ha_rows r_rows_after_where; + inline void on_record_read() { r_rows++; } + inline void on_record_after_where() { r_rows_after_where++; } + + Explain_update() : + r_rows(0), r_rows_after_where(0) + {} virtual int print_explain(Explain_query *query, select_result_sink *output, - uint8 explain_flags); + uint8 explain_flags, bool is_analyze); }; @@ -523,7 +605,7 @@ public: int get_select_id() { return 1; /* always root */ } int print_explain(Explain_query *query, select_result_sink *output, - uint8 explain_flags); + uint8 explain_flags, bool is_analyze); }; @@ -544,7 +626,7 @@ public: virtual int get_select_id() { return 1; /* always root */ } virtual int print_explain(Explain_query *query, select_result_sink *output, - uint8 explain_flags); + uint8 explain_flags, bool is_analyze); }; diff --git a/sql/sql_join_cache.cc b/sql/sql_join_cache.cc index cab9628837c..7f97b70952e 100644 --- a/sql/sql_join_cache.cc +++ b/sql/sql_join_cache.cc @@ -2260,7 +2260,7 @@ enum_nested_loop_state JOIN_CACHE::join_matching_records(bool skip_last) */ goto finish; } - + while (!(error= join_tab_scan->next())) { if (join->thd->check_killed()) @@ -2277,11 +2277,13 @@ enum_nested_loop_state JOIN_CACHE::join_matching_records(bool skip_last) /* Prepare to read matching candidates from the join buffer */ if (prepare_look_for_matches(skip_last)) continue; + join_tab->jbuf_tracker->r_scans++; uchar *rec_ptr; /* Read each possible candidate from the buffer and look for matches */ while ((rec_ptr= get_next_candidate_for_match())) - { + { + join_tab->jbuf_tracker->r_rows++; /* If only the first match is needed, and, it has been already found for the next record read from the join buffer, then the record is skipped. @@ -2451,6 +2453,8 @@ inline bool JOIN_CACHE::check_match(uchar *rec_ptr) if (join_tab->select && join_tab->select->skip_record(join->thd) <= 0) DBUG_RETURN(FALSE); + + join_tab->jbuf_tracker->r_rows_after_where++; if (!join_tab->is_last_inner_table()) DBUG_RETURN(TRUE); @@ -2574,7 +2578,7 @@ finish: none */ -void JOIN_CACHE::save_explain_data(struct st_explain_bka_type *explain) +void JOIN_CACHE::save_explain_data(EXPLAIN_BKA_TYPE *explain) { explain->incremental= MY_TEST(prev_cache); @@ -2619,14 +2623,14 @@ static void add_mrr_explain_info(String *str, uint mrr_mode, handler *file) } } -void JOIN_CACHE_BKA::save_explain_data(struct st_explain_bka_type *explain) +void JOIN_CACHE_BKA::save_explain_data(EXPLAIN_BKA_TYPE *explain) { JOIN_CACHE::save_explain_data(explain); add_mrr_explain_info(&explain->mrr_type, mrr_mode, join_tab->table->file); } -void JOIN_CACHE_BKAH::save_explain_data(struct st_explain_bka_type *explain) +void JOIN_CACHE_BKAH::save_explain_data(EXPLAIN_BKA_TYPE *explain) { JOIN_CACHE::save_explain_data(explain); add_mrr_explain_info(&explain->mrr_type, mrr_mode, join_tab->table->file); @@ -3333,6 +3337,7 @@ int JOIN_TAB_SCAN::open() { save_or_restore_used_tabs(join_tab, FALSE); is_first_record= TRUE; + join_tab->tracker->r_scans++; return join_init_read_record(join_tab); } @@ -3371,8 +3376,14 @@ int JOIN_TAB_SCAN::next() is_first_record= FALSE; else err= info->read_record(info); - if (!err && table->vfield) - update_virtual_fields(thd, table); + + if (!err) + { + join_tab->tracker->r_rows++; + if (table->vfield) + update_virtual_fields(thd, table); + } + while (!err && select && (skip_rc= select->skip_record(thd)) <= 0) { if (thd->check_killed() || skip_rc < 0) @@ -3382,9 +3393,16 @@ int JOIN_TAB_SCAN::next() meet the condition pushed to the table join_tab. */ err= info->read_record(info); - if (!err && table->vfield) - update_virtual_fields(thd, table); - } + if (!err) + { + join_tab->tracker->r_rows++; + if (table->vfield) + update_virtual_fields(thd, table); + } + } + + if (!err) + join_tab->tracker->r_rows_after_where++; return err; } diff --git a/sql/sql_join_cache.h b/sql/sql_join_cache.h index a3e69f92e34..b83ccf4b032 100644 --- a/sql/sql_join_cache.h +++ b/sql/sql_join_cache.h @@ -63,7 +63,7 @@ typedef struct st_cache_field { class JOIN_TAB_SCAN; -struct st_explain_bka_type; +class EXPLAIN_BKA_TYPE; /* JOIN_CACHE is the base class to support the implementations of @@ -662,7 +662,7 @@ public: enum_nested_loop_state join_records(bool skip_last); /* Add a comment on the join algorithm employed by the join cache */ - virtual void save_explain_data(struct st_explain_bka_type *explain); + virtual void save_explain_data(EXPLAIN_BKA_TYPE *explain); THD *thd(); @@ -1340,7 +1340,7 @@ public: /* Check index condition of the joined table for a record from BKA cache */ bool skip_index_tuple(range_id_t range_info); - void save_explain_data(struct st_explain_bka_type *explain); + void save_explain_data(EXPLAIN_BKA_TYPE *explain); }; @@ -1431,5 +1431,5 @@ public: /* Check index condition of the joined table for a record from BKAH cache */ bool skip_index_tuple(range_id_t range_info); - void save_explain_data(struct st_explain_bka_type *explain); + void save_explain_data(EXPLAIN_BKA_TYPE *explain); }; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index c549a7be2cc..cd9f9238f71 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -483,6 +483,7 @@ void lex_start(THD *thd) if (lex->select_lex.group_list_ptrs) lex->select_lex.group_list_ptrs->clear(); lex->describe= 0; + lex->analyze_stmt= 0; lex->subqueries= FALSE; lex->context_analysis_only= 0; lex->derived_tables= 0; @@ -4181,12 +4182,12 @@ bool st_select_lex::is_merged_child_of(st_select_lex *ancestor) */ int LEX::print_explain(select_result_sink *output, uint8 explain_flags, - bool *printed_anything) + bool is_analyze, bool *printed_anything) { int res; if (explain && explain->have_query_plan()) { - res= explain->print_explain(output, explain_flags); + res= explain->print_explain(output, explain_flags, is_analyze); *printed_anything= true; } else diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 76288e94c75..70a793541af 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -2268,6 +2268,7 @@ public: void save_explain_data(Explain_query *query); void save_explain_data_intern(Explain_query *query, Explain_update *eu); + virtual ~Update_plan() {} Update_plan(MEM_ROOT *mem_root_arg) : @@ -2459,6 +2460,7 @@ struct LEX: public Query_tables_list */ uint table_count; uint8 describe; + bool analyze_stmt; /* TRUE<=> this is "ANALYZE $stmt" */ /* A flag that indicates what kinds of derived tables are present in the query (0 if no derived tables, otherwise a combination of flags @@ -2735,7 +2737,7 @@ struct LEX: public Query_tables_list } int print_explain(select_result_sink *output, uint8 explain_flags, - bool *printed_anything); + bool is_analyze, bool *printed_anything); }; diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index c0c44cf4ec7..5e305bd4541 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -3581,7 +3581,6 @@ end_with_restore_list: { DBUG_ASSERT(first_table == all_tables && first_table != 0); TABLE_LIST *aux_tables= thd->lex->auxiliary_table_list.first; - bool explain= MY_TEST(lex->describe); multi_delete *result; if ((res= multi_delete_precheck(thd, all_tables))) @@ -3627,7 +3626,7 @@ end_with_restore_list: result->abort_result_set(); /* for both DELETE and EXPLAIN DELETE */ else { - if (explain) + if (lex->describe || lex->analyze_stmt) res= thd->lex->explain->send_explain(thd); } delete result; @@ -5223,7 +5222,7 @@ static bool execute_sqlcom_select(THD *thd, TABLE_LIST *all_tables) This will call optimize() for all parts of query. The query plan is printed out below. */ - res= mysql_explain_union(thd, &thd->lex->unit, result); + res= mysql_explain_union(thd, &lex->unit, result); /* Print EXPLAIN only if we don't have an error */ if (!res) @@ -5233,7 +5232,7 @@ static bool execute_sqlcom_select(THD *thd, TABLE_LIST *all_tables) top-level LIMIT */ result->reset_offset_limit(); - thd->lex->explain->print_explain(result, thd->lex->describe); + lex->explain->print_explain(result, lex->describe, lex->analyze_stmt); if (lex->describe & DESCRIBE_EXTENDED) { char buff[1024]; @@ -5243,7 +5242,7 @@ static bool execute_sqlcom_select(THD *thd, TABLE_LIST *all_tables) The warnings system requires input in utf8, @see mysqld_show_warnings(). */ - thd->lex->unit.print(&str, QT_TO_SYSTEM_CHARSET); + lex->unit.print(&str, QT_TO_SYSTEM_CHARSET); push_warning(thd, Sql_condition::WARN_LEVEL_NOTE, ER_YES, str.c_ptr_safe()); } @@ -5257,12 +5256,37 @@ static bool execute_sqlcom_select(THD *thd, TABLE_LIST *all_tables) } else { - if (!result && !(result= new select_send())) - return 1; /* purecov: inspected */ + select_result *save_result; + Protocol *save_protocol; + if (lex->analyze_stmt) + { + save_result= result; + result= new select_send_analyze(); + save_protocol= thd->protocol; + thd->protocol= new Protocol_discard(thd); + } + else + { + if (!result && !(result= new select_send())) + return 1; /* purecov: inspected */ + } query_cache_store_query(thd, all_tables); res= handle_select(thd, lex, result, 0); if (result != lex->result) delete result; + + if (lex->analyze_stmt) + { + result= save_result; + if (!result && !(result= new select_send())) + return 1; + delete thd->protocol; + thd->protocol= save_protocol; + thd->lex->explain->send_explain(thd); + + if (result != lex->result) + delete result; + } } } /* Count number of empty select queries */ diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 2e4227ed8a0..39fc81afc02 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1032,6 +1032,9 @@ int JOIN::optimize() subquery), returns 1 - another JOIN::optimize() call made, and now join->optimize() will return 0, even though we never had a query plan. + + Can have QEP_NOT_PRESENT_YET for degenerate queries (for example, + SELECT * FROM tbl LIMIT 0) */ if (was_optimized != optimized && !res && have_query_plan != QEP_DELETED) { @@ -1871,6 +1874,9 @@ TODO: make view to decide if it is possible to write to WHERE directly or make S } } + if ((select_lex->options & OPTION_SCHEMA_TABLE)) + optimize_schema_tables_reads(this); + tmp_having= having; if (select_options & SELECT_DESCRIBE) { @@ -1917,6 +1923,7 @@ setup_subq_exit: error= 0; derived_exit: + select_lex->mark_const_derived(zero_result_cause); DBUG_RETURN(0); } @@ -2057,6 +2064,7 @@ int JOIN::init_execution() &join_tab[const_tables].table-> keys_in_use_for_order_by)) order=0; + join_tab[const_tables].update_explain_data(const_tables); } } @@ -2351,6 +2359,20 @@ void JOIN::save_explain_data(Explain_query *output, bool can_overwrite, } save_explain_data_intern(thd->lex->explain, need_tmp_table, need_order, distinct, message); + return; + } + + /* + Can have join_tab==NULL for degenerate cases (e.g. SELECT .. UNION ... SELECT LIMIT 0) + */ + if (select_lex == select_lex->master_unit()->fake_select_lex && join_tab) + { + /* + This is fake_select_lex. It has no query plan, but we need to set up a + tracker for ANALYZE + */ + Explain_union *eu= output->get_union(select_lex->master_unit()->first_select()->select_number); + join_tab[0].tracker= eu->get_fake_select_lex_tracker(); } } @@ -2365,15 +2387,6 @@ void JOIN::exec() ); exec_inner(); - if (!exec_saved_explain) - { - save_explain_data(thd->lex->explain, true /* can overwrite */, - need_tmp, - order != 0 && !skip_sort_order, - select_distinct); - exec_saved_explain= true; - } - DBUG_EXECUTE_IF("show_explain_probe_join_exec_end", if (dbug_user_var_equals_int(thd, "show_explain_probe_select_id", @@ -2548,15 +2561,19 @@ void JOIN::exec_inner() simple_order= simple_group; skip_sort_order= 0; } + bool made_call= false; if (order && (order != group_list || !(select_options & SELECT_BIG_RESULT)) && (const_tables == table_count || ((simple_order || skip_sort_order) && - test_if_skip_sort_order(&join_tab[const_tables], order, + (made_call=true) && + test_if_skip_sort_order(&join_tab[const_tables], order, select_limit, 0, &join_tab[const_tables].table-> keys_in_use_for_query)))) order=0; + if (made_call) + join_tab[const_tables].update_explain_data(const_tables); having= tmp_having; select_describe(this, need_tmp, order != 0 && !skip_sort_order, @@ -8966,6 +8983,20 @@ JOIN::make_simple_join(JOIN *parent, TABLE *temp_table) join_tab->read_first_record= join_init_read_record; join_tab->join= this; join_tab->ref.key_parts= 0; + + uint select_nr= select_lex->select_number; + if (select_nr == INT_MAX) + { + /* this is a fake_select_lex of a union */ + select_nr= select_lex->master_unit()->first_select()->select_number; + join_tab->tracker= thd->lex->explain->get_union(select_nr)-> + get_tmptable_read_tracker(); + } + else + { + join_tab->tracker= thd->lex->explain->get_select(select_nr)-> + get_using_temporary_read_tracker(); + } bzero((char*) &join_tab->read_record,sizeof(join_tab->read_record)); temp_table->status=0; temp_table->null_row=0; @@ -17546,6 +17577,8 @@ sub_select(JOIN *join,JOIN_TAB *join_tab,bool end_of_records) (*join_tab->next_select)(join,join_tab+1,end_of_records); DBUG_RETURN(nls); } + join_tab->tracker->r_scans++; + int error; enum_nested_loop_state rc= NESTED_LOOP_OK; READ_RECORD *info= &join_tab->read_record; @@ -17681,6 +17714,8 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab, DBUG_RETURN(NESTED_LOOP_KILLED); /* purecov: inspected */ } + join_tab->tracker->r_rows++; + if (join_tab->table->vfield) update_virtual_fields(join->thd, join_tab->table); @@ -17699,6 +17734,7 @@ evaluate_join_record(JOIN *join, JOIN_TAB *join_tab, There is no select condition or the attached pushed down condition is true => a match is found. */ + join_tab->tracker->r_rows_after_where++; bool found= 1; while (join_tab->first_unmatched && found) { @@ -20513,7 +20549,12 @@ create_sort_index(THD *thd, JOIN *join, ORDER *order, test_if_skip_sort_order(tab,order,select_limit,0, is_order_by ? &table->keys_in_use_for_order_by : &table->keys_in_use_for_group_by)) + { + tab->update_explain_data(join->const_tables); DBUG_RETURN(0); + } + tab->update_explain_data(join->const_tables); + for (ORDER *ord= join->order; ord; ord= ord->next) length++; if (!(join->sortorder= @@ -22926,10 +22967,11 @@ void JOIN::clear() /* Print an EXPLAIN line with all NULLs and given message in the 'Extra' column + TODO: is_analyze */ int print_explain_message_line(select_result_sink *result, - uint8 options, + uint8 options, bool is_analyze, uint select_number, const char *select_type, ha_rows *rows, @@ -22962,8 +23004,16 @@ int print_explain_message_line(select_result_sink *result, else item_list.push_back(item_null); + /* `r_rows` */ + if (is_analyze) + item_list.push_back(item_null); + /* `filtered` */ - if (options & DESCRIBE_EXTENDED) + if (is_analyze || options & DESCRIBE_EXTENDED) + item_list.push_back(item_null); + + /* `r_filtered` */ + if (is_analyze) item_list.push_back(item_null); /* `Extra` */ @@ -23014,7 +23064,7 @@ void make_possible_keys_line(TABLE *table, key_map possible_keys, String *line) */ int print_explain_row(select_result_sink *result, - uint8 options, + uint8 options, bool is_analyze, uint select_number, const char *select_type, const char *table_name, @@ -23025,6 +23075,8 @@ int print_explain_row(select_result_sink *result, const char *key_len, const char *ref, ha_rows *rows, + ha_rows *r_rows, + double r_filtered, const char *extra) { const CHARSET_INFO *cs= system_charset_info; @@ -23076,12 +23128,20 @@ int print_explain_row(select_result_sink *result, } else item_list.push_back(item_null); + + /* 'r_rows' */ + if (is_analyze) + item_list.push_back(item_null); /* 'filtered' */ const double filtered=100.0; - if (options & DESCRIBE_EXTENDED) + if (options & DESCRIBE_EXTENDED || is_analyze) item_list.push_back(new Item_float(filtered, 2)); + /* 'r_filtered' */ + if (is_analyze) + item_list.push_back(new Item_float(r_filtered, 2)); + /* 'Extra' */ if (extra) item_list.push_back(new Item_string(extra, strlen(extra), cs)); @@ -23211,6 +23271,438 @@ void append_possible_keys(String *str, TABLE *table, key_map possible_keys) } } +// TODO: this function is only applicable for the first non-const optimization +// join tab. +void JOIN_TAB::update_explain_data(uint idx) +{ + if (this == first_breadth_first_tab(join, WALK_OPTIMIZATION_TABS) + join->const_tables && + join->select_lex->select_number != INT_MAX && + join->select_lex->select_number != UINT_MAX) + { + Explain_table_access *eta= new Explain_table_access(); + JOIN_TAB* const first_top_tab= first_breadth_first_tab(join, WALK_OPTIMIZATION_TABS); + save_explain_data(eta, join->const_table_map, join->select_distinct, first_top_tab); + + Explain_select *sel= join->thd->lex->explain->get_select(join->select_lex->select_number); + idx -= my_count_bits(join->eliminated_tables); + sel->replace_table(idx, eta); + } +} + + +void JOIN_TAB::save_explain_data(Explain_table_access *eta, table_map prefix_tables, + bool distinct, JOIN_TAB *first_top_tab) +{ + int quick_type; + const CHARSET_INFO *cs= system_charset_info; + + JOIN_TAB *tab= this; + THD *thd=join->thd; + + TABLE *table=tab->table; + TABLE_LIST *table_list= tab->table->pos_in_table_list; + char buff4[512]; + my_bool key_read; + char table_name_buffer[SAFE_NAME_LEN]; + String tmp4(buff4,sizeof(buff4),cs); + KEY *key_info= 0; + uint key_len= 0; + tmp4.length(0); + quick_type= -1; + QUICK_SELECT_I *quick= NULL; + + eta->key.set(thd->mem_root, NULL, (uint)-1); + eta->quick_info= NULL; + + tab->tracker= &eta->tracker; + tab->jbuf_tracker= &eta->jbuf_tracker; + + /* id */ + if (tab->bush_root_tab) + { + JOIN_TAB *first_sibling= tab->bush_root_tab->bush_children->start; + eta->sjm_nest_select_id= first_sibling->emb_sj_nest->sj_subq_pred->get_identifier(); + } + else + eta->sjm_nest_select_id= 0; + + /* select_type is kept in Explain_select */ + + /* table */ + if (table->derived_select_number) + { + /* Derived table name generation */ + int len= my_snprintf(table_name_buffer, sizeof(table_name_buffer)-1, + "<derived%u>", + table->derived_select_number); + eta->table_name.copy(table_name_buffer, len, cs); + } + else if (tab->bush_children) + { + JOIN_TAB *ctab= tab->bush_children->start; + /* table */ + int len= my_snprintf(table_name_buffer, + sizeof(table_name_buffer)-1, + "<subquery%d>", + ctab->emb_sj_nest->sj_subq_pred->get_identifier()); + eta->table_name.copy(table_name_buffer, len, cs); + } + else + { + TABLE_LIST *real_table= table->pos_in_table_list; + /* + When multi-table UPDATE/DELETE does updates/deletes to a VIEW, the view + is merged in a certain particular way (grep for DT_MERGE_FOR_INSERT). + + As a result, view's underlying tables have $tbl->pos_in_table_list={view}. + We don't want to print view name in EXPLAIN, we want underlying table's + alias (like specified in the view definition). + */ + if (real_table->merged_for_insert) + { + TABLE_LIST *view_child= real_table->view->select_lex.table_list.first; + for (;view_child; view_child= view_child->next_local) + { + if (view_child->table == table) + { + real_table= view_child; + break; + } + } + } + eta->table_name.copy(real_table->alias, strlen(real_table->alias), cs); + } + + /* "partitions" column */ + { +#ifdef WITH_PARTITION_STORAGE_ENGINE + partition_info *part_info; + if (!table->derived_select_number && + (part_info= table->part_info)) + { + make_used_partitions_str(part_info, &eta->used_partitions); + eta->used_partitions_set= true; + } + else + eta->used_partitions_set= false; +#else + /* just produce empty column if partitioning is not compiled in */ + eta->used_partitions_set= false; +#endif + } + + /* "type" column */ + enum join_type tab_type= tab->type; + if ((tab->type == JT_ALL || tab->type == JT_HASH) && + tab->select && tab->select->quick && tab->use_quick != 2) + { + quick= tab->select->quick; + quick_type= tab->select->quick->get_type(); + if ((quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE) || + (quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT) || + (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT) || + (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION)) + tab_type= tab->type == JT_ALL ? JT_INDEX_MERGE : JT_HASH_INDEX_MERGE; + else + tab_type= tab->type == JT_ALL ? JT_RANGE : JT_HASH_RANGE; + } + eta->type= tab_type; + + /* Build "possible_keys" value */ + append_possible_keys(&eta->possible_keys_str, table, tab->keys); + + /* Build "key", "key_len", and "ref" */ + if (tab_type == JT_NEXT) + { + key_info= table->key_info+tab->index; + key_len= key_info->key_length; + } + else if (tab->ref.key_parts) + { + key_info= tab->get_keyinfo_by_key_no(tab->ref.key); + key_len= tab->ref.key_length; + } + + /* + In STRAIGHT_JOIN queries, there can be join tabs with JT_CONST type + that still have quick selects. + */ + if (tab->select && tab->select->quick && tab_type != JT_CONST) + { + eta->quick_info= tab->select->quick->get_explain(thd->mem_root); + } + + if (key_info) /* 'index' or 'ref' access */ + { + eta->key.set(thd->mem_root, key_info->name, key_len); + + if (tab->ref.key_parts && tab_type != JT_FT) + { + store_key **ref=tab->ref.key_copy; + for (uint kp= 0; kp < tab->ref.key_parts; kp++) + { + if (tmp4.length()) + tmp4.append(','); + + if ((key_part_map(1) << kp) & tab->ref.const_ref_part_map) + tmp4.append("const"); + else + { + tmp4.append((*ref)->name(), strlen((*ref)->name()), cs); + ref++; + } + } + } + } + + if (tab_type == JT_HASH_NEXT) /* full index scan + hash join */ + { + eta->hash_next_key.set(thd->mem_root, + table->key_info[tab->index].name, + table->key_info[tab->index].key_length); + } + + if (key_info) + { + if (key_info && tab_type != JT_NEXT) + { + eta->ref.copy(tmp4); + eta->ref_set= true; + } + else + eta->ref_set= false; + } + else + { + if (table_list && /* SJM bushes don't have table_list */ + table_list->schema_table && + table_list->schema_table->i_s_requested_object & OPTIMIZE_I_S_TABLE) + { + IS_table_read_plan *is_table_read_plan= table_list->is_table_read_plan; + const char *tmp_buff; + int f_idx; + StringBuffer<64> key_name_buf; + if (is_table_read_plan->has_db_lookup_value()) + { + /* The "key" has the name of the column referring to the database */ + f_idx= table_list->schema_table->idx_field1; + tmp_buff= table_list->schema_table->fields_info[f_idx].field_name; + key_name_buf.append(tmp_buff, strlen(tmp_buff), cs); + } + if (is_table_read_plan->has_table_lookup_value()) + { + if (is_table_read_plan->has_db_lookup_value()) + key_name_buf.append(','); + + f_idx= table_list->schema_table->idx_field2; + tmp_buff= table_list->schema_table->fields_info[f_idx].field_name; + key_name_buf.append(tmp_buff, strlen(tmp_buff), cs); + } + + if (key_name_buf.length()) + eta->key.set(thd->mem_root, key_name_buf.c_ptr_safe(), -1); + } + eta->ref_set= false; + } + + /* "rows" */ + if (table_list /* SJM bushes don't have table_list */ && + table_list->schema_table) + { + /* I_S tables have rows=extra=NULL */ + eta->rows_set= false; + eta->filtered_set= false; + } + else + { + double examined_rows= tab->get_examined_rows(); + + eta->rows_set= true; + eta->rows= (ha_rows) examined_rows; + + /* "filtered" */ + float f= 0.0; + if (examined_rows) + { + double pushdown_cond_selectivity= tab->cond_selectivity; + if (pushdown_cond_selectivity == 1.0) + f= (float) (100.0 * tab->records_read / examined_rows); + else + f= (float) (100.0 * pushdown_cond_selectivity); + } + set_if_smaller(f, 100.0); + eta->filtered_set= true; + eta->filtered= f; + } + + /* Build "Extra" field and save it */ + key_read=table->key_read; + if ((tab_type == JT_NEXT || tab_type == JT_CONST) && + table->covering_keys.is_set(tab->index)) + key_read=1; + if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT && + !((QUICK_ROR_INTERSECT_SELECT*)quick)->need_to_fetch_row) + key_read=1; + + if (tab->info) + { + eta->push_extra(tab->info); + } + else if (tab->packed_info & TAB_INFO_HAVE_VALUE) + { + if (tab->packed_info & TAB_INFO_USING_INDEX) + eta->push_extra(ET_USING_INDEX); + if (tab->packed_info & TAB_INFO_USING_WHERE) + eta->push_extra(ET_USING_WHERE); + if (tab->packed_info & TAB_INFO_FULL_SCAN_ON_NULL) + eta->push_extra(ET_FULL_SCAN_ON_NULL_KEY); + } + else + { + uint keyno= MAX_KEY; + if (tab->ref.key_parts) + keyno= tab->ref.key; + else if (tab->select && quick) + keyno = quick->index; + + if (keyno != MAX_KEY && keyno == table->file->pushed_idx_cond_keyno && + table->file->pushed_idx_cond) + eta->push_extra(ET_USING_INDEX_CONDITION); + else if (tab->cache_idx_cond) + eta->push_extra(ET_USING_INDEX_CONDITION_BKA); + + if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION || + quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT || + quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT || + quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE) + { + eta->push_extra(ET_USING); + } + if (tab->select) + { + if (tab->use_quick == 2) + { + eta->push_extra(ET_RANGE_CHECKED_FOR_EACH_RECORD); + eta->range_checked_map= tab->keys; + } + else if (tab->select->cond || + (tab->cache_select && tab->cache_select->cond)) + { + const COND *pushed_cond= tab->table->file->pushed_cond; + + if (((thd->variables.optimizer_switch & + OPTIMIZER_SWITCH_ENGINE_CONDITION_PUSHDOWN) || + (tab->table->file->ha_table_flags() & + HA_MUST_USE_TABLE_CONDITION_PUSHDOWN)) && + pushed_cond) + { + eta->push_extra(ET_USING_WHERE_WITH_PUSHED_CONDITION); + /* + psergey-todo: what to do? This was useful with NDB only. + + if (explain_flags & DESCRIBE_EXTENDED) + { + extra.append(STRING_WITH_LEN(": ")); + ((COND *)pushed_cond)->print(&extra, QT_ORDINARY); + } + */ + } + else + eta->push_extra(ET_USING_WHERE); + } + } + if (table_list /* SJM bushes don't have table_list */ && + table_list->schema_table && + table_list->schema_table->i_s_requested_object & OPTIMIZE_I_S_TABLE) + { + if (!table_list->table_open_method) + eta->push_extra(ET_SKIP_OPEN_TABLE); + else if (table_list->table_open_method == OPEN_FRM_ONLY) + eta->push_extra(ET_OPEN_FRM_ONLY); + else + eta->push_extra(ET_OPEN_FULL_TABLE); + /* psergey-note: the following has a bug.*/ + if (table_list->is_table_read_plan->has_db_lookup_value() && + table_list->is_table_read_plan->has_table_lookup_value()) + eta->push_extra(ET_SCANNED_0_DATABASES); + else if (table_list->is_table_read_plan->has_db_lookup_value() || + table_list->is_table_read_plan->has_table_lookup_value()) + eta->push_extra(ET_SCANNED_1_DATABASE); + else + eta->push_extra(ET_SCANNED_ALL_DATABASES); + } + if (key_read) + { + if (quick_type == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX) + { + QUICK_GROUP_MIN_MAX_SELECT *qgs= + (QUICK_GROUP_MIN_MAX_SELECT *) tab->select->quick; + eta->push_extra(ET_USING_INDEX_FOR_GROUP_BY); + eta->loose_scan_is_scanning= qgs->loose_scan_is_scanning(); + } + else + eta->push_extra(ET_USING_INDEX); + } + if (table->reginfo.not_exists_optimize) + eta->push_extra(ET_NOT_EXISTS); + + if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE) + { + explain_append_mrr_info((QUICK_RANGE_SELECT*)(tab->select->quick), + &eta->mrr_type); + if (eta->mrr_type.length() > 0) + eta->push_extra(ET_USING_MRR); + } + + if (distinct & test_all_bits(prefix_tables, join->select_list_used_tables)) + eta->push_extra(ET_DISTINCT); + if (tab->loosescan_match_tab) + { + eta->push_extra(ET_LOOSESCAN); + } + + if (tab->first_weedout_table) + eta->push_extra(ET_START_TEMPORARY); + if (tab->check_weed_out_table) + eta->push_extra(ET_END_TEMPORARY); + else if (tab->do_firstmatch) + { + if (tab->do_firstmatch == /*join->join_tab*/ first_top_tab - 1) + eta->push_extra(ET_FIRST_MATCH); + else + { + eta->push_extra(ET_FIRST_MATCH); + TABLE *prev_table=tab->do_firstmatch->table; + if (prev_table->derived_select_number) + { + char namebuf[NAME_LEN]; + /* Derived table name generation */ + int len= my_snprintf(namebuf, sizeof(namebuf)-1, + "<derived%u>", + prev_table->derived_select_number); + eta->firstmatch_table_name.append(namebuf, len); + } + else + eta->firstmatch_table_name.append(prev_table->pos_in_table_list->alias); + } + } + + for (uint part= 0; part < tab->ref.key_parts; part++) + { + if (tab->ref.cond_guards[part]) + { + eta->push_extra(ET_FULL_SCAN_ON_NULL_KEY); + break; + } + } + + if (tab->cache) + { + eta->push_extra(ET_USING_JOIN_BUFFER); + tab->cache->save_explain_data(&eta->bka_type); + } + } +} /* Save Query Plan Footprint @@ -23225,9 +23717,6 @@ int JOIN::save_explain_data_intern(Explain_query *output, bool need_tmp_table, { Explain_node *explain_node; JOIN *join= this; /* Legacy: this code used to be a non-member function */ - THD *thd=join->thd; - const CHARSET_INFO *cs= system_charset_info; - int quick_type; int error= 0; DBUG_ENTER("JOIN::save_explain_data_intern"); DBUG_PRINT("info", ("Select 0x%lx, type %s, message %s", @@ -23268,27 +23757,9 @@ int JOIN::save_explain_data_intern(Explain_query *output, bool need_tmp_table, for (JOIN_TAB *tab= first_breadth_first_tab(join, WALK_OPTIMIZATION_TABS); tab; tab= next_breadth_first_tab(join, WALK_OPTIMIZATION_TABS, tab)) { - uint select_id; - if (tab->bush_root_tab) - { - JOIN_TAB *first_sibling= tab->bush_root_tab->bush_children->start; - select_id= first_sibling->emb_sj_nest->sj_subq_pred->get_identifier(); - } - else - select_id= join->select_lex->select_number; - TABLE *table=tab->table; - TABLE_LIST *table_list= tab->table->pos_in_table_list; - char buff4[512]; - my_bool key_read; - char table_name_buffer[SAFE_NAME_LEN]; - String tmp4(buff4,sizeof(buff4),cs); - KEY *key_info= 0; - uint key_len= 0; - tmp4.length(0); - quick_type= -1; - QUICK_SELECT_I *quick= NULL; JOIN_TAB *saved_join_tab= NULL; + TABLE *table=tab->table; /* Don't show eliminated tables */ if (table->map & join->eliminated_tables) @@ -23306,383 +23777,20 @@ int JOIN::save_explain_data_intern(Explain_query *output, bool need_tmp_table, Explain_table_access *eta= new (output->mem_root) Explain_table_access; xpl_sel->add_table(eta); - eta->key.set(thd->mem_root, NULL, (uint)-1); - eta->quick_info= NULL; - - /* id */ - if (tab->bush_root_tab) - eta->sjm_nest_select_id= select_id; - else - eta->sjm_nest_select_id= 0; - - /* select_type */ - xpl_sel->select_type= join->select_lex->type; - - /* table */ - if (table->derived_select_number) - { - /* Derived table name generation */ - int len= my_snprintf(table_name_buffer, sizeof(table_name_buffer)-1, - "<derived%u>", - table->derived_select_number); - eta->table_name.copy(table_name_buffer, len, cs); - } - else if (tab->bush_children) - { - JOIN_TAB *ctab= tab->bush_children->start; - /* table */ - int len= my_snprintf(table_name_buffer, - sizeof(table_name_buffer)-1, - "<subquery%d>", - ctab->emb_sj_nest->sj_subq_pred->get_identifier()); - eta->table_name.copy(table_name_buffer, len, cs); - } - else - { - TABLE_LIST *real_table= table->pos_in_table_list; - eta->table_name.copy(real_table->alias, strlen(real_table->alias), cs); - } - - /* "partitions" column */ - { -#ifdef WITH_PARTITION_STORAGE_ENGINE - partition_info *part_info; - if (!table->derived_select_number && - (part_info= table->part_info)) - { - make_used_partitions_str(part_info, &eta->used_partitions); - eta->used_partitions_set= true; - } - else - eta->used_partitions_set= false; -#else - /* just produce empty column if partitioning is not compiled in */ - eta->used_partitions_set= false; -#endif - } - - /* "type" column */ - enum join_type tab_type= tab->type; - if ((tab->type == JT_ALL || tab->type == JT_HASH) && - tab->select && tab->select->quick && tab->use_quick != 2) - { - quick= tab->select->quick; - quick_type= tab->select->quick->get_type(); - if ((quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE) || - (quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT) || - (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT) || - (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION)) - tab_type= tab->type == JT_ALL ? JT_INDEX_MERGE : JT_HASH_INDEX_MERGE; - else - tab_type= tab->type == JT_ALL ? JT_RANGE : JT_HASH_RANGE; - } - eta->type= tab_type; - - /* Build "possible_keys" value */ - append_possible_keys(&eta->possible_keys_str, table, tab->keys); - - /* Build "key", "key_len", and "ref" */ - if (tab_type == JT_NEXT) - { - key_info= table->key_info+tab->index; - key_len= key_info->key_length; - } - else if (tab->ref.key_parts) - { - key_info= tab->get_keyinfo_by_key_no(tab->ref.key); - key_len= tab->ref.key_length; - } - - /* - In STRAIGHT_JOIN queries, there can be join tabs with JT_CONST type - that still have quick selects. - */ - if (tab->select && tab->select->quick && tab_type != JT_CONST) - { - eta->quick_info= tab->select->quick->get_explain(thd->mem_root); - } - - if (key_info) /* 'index' or 'ref' access */ - { - eta->key.set(thd->mem_root, key_info->name, key_len); - - if (tab->ref.key_parts && tab_type != JT_FT) - { - store_key **ref=tab->ref.key_copy; - for (uint kp= 0; kp < tab->ref.key_parts; kp++) - { - if (tmp4.length()) - tmp4.append(','); - - if ((key_part_map(1) << kp) & tab->ref.const_ref_part_map) - tmp4.append("const"); - else - { - tmp4.append((*ref)->name(), strlen((*ref)->name()), cs); - ref++; - } - } - } - } - - if (tab_type == JT_HASH_NEXT) /* full index scan + hash join */ - { - eta->hash_next_key.set(thd->mem_root, - table->key_info[tab->index].name, - table->key_info[tab->index].key_length); - } - - if (key_info) - { - if (key_info && tab_type != JT_NEXT) - { - eta->ref.copy(tmp4); - eta->ref_set= true; - } - else - eta->ref_set= false; - } - else - { - if (table_list && /* SJM bushes don't have table_list */ - table_list->schema_table && - table_list->schema_table->i_s_requested_object & OPTIMIZE_I_S_TABLE) - { - const char *tmp_buff; - int f_idx; - StringBuffer<64> key_name_buf; - if (table_list->has_db_lookup_value) - { - /* The "key" has the name of the column referring to the database */ - f_idx= table_list->schema_table->idx_field1; - tmp_buff= table_list->schema_table->fields_info[f_idx].field_name; - key_name_buf.append(tmp_buff, strlen(tmp_buff), cs); - } - if (table_list->has_table_lookup_value) - { - if (table_list->has_db_lookup_value) - key_name_buf.append(','); - - f_idx= table_list->schema_table->idx_field2; - tmp_buff= table_list->schema_table->fields_info[f_idx].field_name; - key_name_buf.append(tmp_buff, strlen(tmp_buff), cs); - } - - if (key_name_buf.length()) - eta->key.set(thd->mem_root, key_name_buf.c_ptr_safe(), -1); - } - eta->ref_set= false; - } - - /* "rows" */ - if (table_list /* SJM bushes don't have table_list */ && - table_list->schema_table) - { - /* I_S tables have rows=extra=NULL */ - eta->rows_set= false; - eta->filtered_set= false; - } - else - { - double examined_rows= tab->get_examined_rows(); - eta->rows_set= true; - eta->rows= (ha_rows) examined_rows; + tab->save_explain_data(eta, used_tables, distinct, first_top_tab); - /* "filtered" */ - float f= 0.0; - if (examined_rows) - { - double pushdown_cond_selectivity= tab->cond_selectivity; - if (pushdown_cond_selectivity == 1.0) - f= (float) (100.0 * tab->records_read / examined_rows); - else - f= (float) (100.0 * pushdown_cond_selectivity); - } - set_if_smaller(f, 100.0); - eta->filtered_set= true; - eta->filtered= f; - } - - /* Build "Extra" field and save it */ - key_read=table->key_read; - if ((tab_type == JT_NEXT || tab_type == JT_CONST) && - table->covering_keys.is_set(tab->index)) - key_read=1; - if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT && - !((QUICK_ROR_INTERSECT_SELECT*)quick)->need_to_fetch_row) - key_read=1; - - if (tab->info) + if (need_tmp_table) { - eta->push_extra(tab->info); + need_tmp_table=0; + xpl_sel->using_temporary= true; } - else if (tab->packed_info & TAB_INFO_HAVE_VALUE) + if (need_order) { - if (tab->packed_info & TAB_INFO_USING_INDEX) - eta->push_extra(ET_USING_INDEX); - if (tab->packed_info & TAB_INFO_USING_WHERE) - eta->push_extra(ET_USING_WHERE); - if (tab->packed_info & TAB_INFO_FULL_SCAN_ON_NULL) - eta->push_extra(ET_FULL_SCAN_ON_NULL_KEY); + need_order=0; + xpl_sel->using_filesort= true; } - else - { - uint keyno= MAX_KEY; - if (tab->ref.key_parts) - keyno= tab->ref.key; - else if (tab->select && quick) - keyno = quick->index; - - if (keyno != MAX_KEY && keyno == table->file->pushed_idx_cond_keyno && - table->file->pushed_idx_cond) - eta->push_extra(ET_USING_INDEX_CONDITION); - else if (tab->cache_idx_cond) - eta->push_extra(ET_USING_INDEX_CONDITION_BKA); - - if (quick_type == QUICK_SELECT_I::QS_TYPE_ROR_UNION || - quick_type == QUICK_SELECT_I::QS_TYPE_ROR_INTERSECT || - quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_INTERSECT || - quick_type == QUICK_SELECT_I::QS_TYPE_INDEX_MERGE) - { - eta->push_extra(ET_USING); - } - if (tab->select) - { - if (tab->use_quick == 2) - { - eta->push_extra(ET_RANGE_CHECKED_FOR_EACH_RECORD); - eta->range_checked_map= tab->keys; - } - else if (tab->select->cond || - (tab->cache_select && tab->cache_select->cond)) - { - const COND *pushed_cond= tab->table->file->pushed_cond; - - if (((thd->variables.optimizer_switch & - OPTIMIZER_SWITCH_ENGINE_CONDITION_PUSHDOWN) || - (tab->table->file->ha_table_flags() & - HA_MUST_USE_TABLE_CONDITION_PUSHDOWN)) && - pushed_cond) - { - eta->push_extra(ET_USING_WHERE_WITH_PUSHED_CONDITION); - /* - psergey-todo: what to do? This was useful with NDB only. - - if (explain_flags & DESCRIBE_EXTENDED) - { - extra.append(STRING_WITH_LEN(": ")); - ((COND *)pushed_cond)->print(&extra, QT_ORDINARY); - } - */ - } - else - eta->push_extra(ET_USING_WHERE); - } - } - if (table_list /* SJM bushes don't have table_list */ && - table_list->schema_table && - table_list->schema_table->i_s_requested_object & OPTIMIZE_I_S_TABLE) - { - if (!table_list->table_open_method) - eta->push_extra(ET_SKIP_OPEN_TABLE); - else if (table_list->table_open_method == OPEN_FRM_ONLY) - eta->push_extra(ET_OPEN_FRM_ONLY); - else - eta->push_extra(ET_OPEN_FULL_TABLE); - /* psergey-note: the following has a bug.*/ - if (table_list->has_db_lookup_value && - table_list->has_table_lookup_value) - eta->push_extra(ET_SCANNED_0_DATABASES); - else if (table_list->has_db_lookup_value || - table_list->has_table_lookup_value) - eta->push_extra(ET_SCANNED_1_DATABASE); - else - eta->push_extra(ET_SCANNED_ALL_DATABASES); - } - if (key_read) - { - if (quick_type == QUICK_SELECT_I::QS_TYPE_GROUP_MIN_MAX) - { - QUICK_GROUP_MIN_MAX_SELECT *qgs= - (QUICK_GROUP_MIN_MAX_SELECT *) tab->select->quick; - eta->push_extra(ET_USING_INDEX_FOR_GROUP_BY); - eta->loose_scan_is_scanning= qgs->loose_scan_is_scanning(); - } - else - eta->push_extra(ET_USING_INDEX); - } - if (table->reginfo.not_exists_optimize) - eta->push_extra(ET_NOT_EXISTS); - - if (quick_type == QUICK_SELECT_I::QS_TYPE_RANGE) - { - explain_append_mrr_info((QUICK_RANGE_SELECT*)(tab->select->quick), - &eta->mrr_type); - if (eta->mrr_type.length() > 0) - eta->push_extra(ET_USING_MRR); - } - - if (need_tmp_table) - { - need_tmp_table=0; - xpl_sel->using_temporary= true; - } - if (need_order) - { - need_order=0; - xpl_sel->using_filesort= true; - } - if (distinct & test_all_bits(used_tables, - join->select_list_used_tables)) - eta->push_extra(ET_DISTINCT); - if (tab->loosescan_match_tab) - { - eta->push_extra(ET_LOOSESCAN); - } - - if (tab->first_weedout_table) - eta->push_extra(ET_START_TEMPORARY); - if (tab->check_weed_out_table) - eta->push_extra(ET_END_TEMPORARY); - else if (tab->do_firstmatch) - { - if (tab->do_firstmatch == /*join->join_tab*/ first_top_tab - 1) - eta->push_extra(ET_FIRST_MATCH); - else - { - eta->push_extra(ET_FIRST_MATCH); - TABLE *prev_table=tab->do_firstmatch->table; - if (prev_table->derived_select_number) - { - char namebuf[NAME_LEN]; - /* Derived table name generation */ - int len= my_snprintf(namebuf, sizeof(namebuf)-1, - "<derived%u>", - prev_table->derived_select_number); - eta->firstmatch_table_name.append(namebuf, len); - } - else - eta->firstmatch_table_name.append(prev_table->pos_in_table_list->alias); - } - } - for (uint part= 0; part < tab->ref.key_parts; part++) - { - if (tab->ref.cond_guards[part]) - { - eta->push_extra(ET_FULL_SCAN_ON_NULL_KEY); - break; - } - } - - if (tab->cache) - { - eta->push_extra(ET_USING_JOIN_BUFFER); - tab->cache->save_explain_data(&eta->bka_type); - } - } - if (saved_join_tab) tab= saved_join_tab; diff --git a/sql/sql_select.h b/sql/sql_select.h index dc86825e8e9..63fd6a6d99f 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -250,7 +250,9 @@ typedef struct st_join_table { /* Special content for EXPLAIN 'Extra' column or NULL if none */ enum explain_extra_tag info; - + + Table_access_tracker *tracker; + Table_access_tracker *jbuf_tracker; /* Bitmap of TAB_INFO_* bits that encodes special line for EXPLAIN 'Extra' column, or 0 if there is no info. @@ -536,6 +538,11 @@ typedef struct st_join_table { } void remove_redundant_bnl_scan_conds(); + + void save_explain_data(Explain_table_access *eta, table_map prefix_tables, + bool distinct, struct st_join_table *first_top_tab); + + void update_explain_data(uint idx); } JOIN_TAB; @@ -1342,7 +1349,6 @@ public: sjm_lookup_tables= 0; filesort_found_rows= false; - exec_saved_explain= false; /* The following is needed because JOIN::cleanup(true) may be called for joins for which JOIN::optimize was aborted with an error before a proper @@ -1351,13 +1357,6 @@ public: table_access_tabs= NULL; } - /* - TRUE <=> There was a JOIN::exec() call, which saved this JOIN's EXPLAIN. - The idea is that we also save at the end of JOIN::optimize(), but that - might not be the final plan. - */ - bool exec_saved_explain; - int prepare(Item ***rref_pointer_array, TABLE_LIST *tables, uint wind_num, COND *conds, uint og_num, ORDER *order, bool skip_order_by, ORDER *group, Item *having, ORDER *proc_param, SELECT_LEX *select, @@ -1848,14 +1847,14 @@ void push_index_cond(JOIN_TAB *tab, uint keyno); /* EXPLAIN-related utility functions */ int print_explain_message_line(select_result_sink *result, - uint8 options, + uint8 options, bool is_analyze, uint select_number, const char *select_type, ha_rows *rows, const char *message); void explain_append_mrr_info(QUICK_RANGE_SELECT *quick, String *res); int print_explain_row(select_result_sink *result, - uint8 options, + uint8 options, bool is_analyze, uint select_number, const char *select_type, const char *table_name, @@ -1866,6 +1865,8 @@ int print_explain_row(select_result_sink *result, const char *key_len, const char *ref, ha_rows *rows, + ha_rows *r_rows, + double r_filtered, const char *extra); void make_possible_keys_line(TABLE *table, key_map possible_keys, String *line); diff --git a/sql/sql_show.cc b/sql/sql_show.cc index bbe5f780c25..79c444ea442 100644 --- a/sql/sql_show.cc +++ b/sql/sql_show.cc @@ -121,41 +121,6 @@ append_algorithm(TABLE_LIST *table, String *buff); static COND * make_cond_for_info_schema(COND *cond, TABLE_LIST *table); -/** - Condition pushdown used for INFORMATION_SCHEMA / SHOW queries. - This structure is to implement an optimization when - accessing data dictionary data in the INFORMATION_SCHEMA - or SHOW commands. - When the query contain a TABLE_SCHEMA or TABLE_NAME clause, - narrow the search for data based on the constraints given. -*/ -typedef struct st_lookup_field_values -{ - /** - Value of a TABLE_SCHEMA clause. - Note that this value length may exceed @c NAME_LEN. - @sa wild_db_value - */ - LEX_STRING db_value; - /** - Value of a TABLE_NAME clause. - Note that this value length may exceed @c NAME_LEN. - @sa wild_table_value - */ - LEX_STRING table_value; - /** - True when @c db_value is a LIKE clause, - false when @c db_value is an '=' clause. - */ - bool wild_db_value; - /** - True when @c table_value is a LIKE clause, - false when @c table_value is an '=' clause. - */ - bool wild_table_value; -} LOOKUP_FIELD_VALUES; - - bool get_lookup_field_values(THD *, COND *, TABLE_LIST *, LOOKUP_FIELD_VALUES *); /*************************************************************************** @@ -2395,7 +2360,7 @@ void Show_explain_request::call_in_target_thread() DBUG_ASSERT(current_thd == target_thd); set_current_thd(request_thd); if (target_thd->lex->print_explain(explain_buf, 0 /* explain flags*/, - &printed_anything)) + false /*TODO: analyze? */, &printed_anything)) { failed_to_produce= TRUE; } @@ -4676,6 +4641,10 @@ public: from frm files and storage engine are filled by the function get_all_tables(). + @note This function assumes optimize_for_get_all_tables() has been + run for the table and produced a "read plan" in + tables->is_table_read_plan. + @param[in] thd thread handler @param[in] tables I_S table @param[in] cond 'WHERE' condition @@ -4692,16 +4661,16 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond) TABLE_LIST table_acl_check; SELECT_LEX *lsel= tables->schema_select_lex; ST_SCHEMA_TABLE *schema_table= tables->schema_table; - LOOKUP_FIELD_VALUES lookup_field_vals; + IS_table_read_plan *plan= tables->is_table_read_plan; enum enum_schema_tables schema_table_idx; Dynamic_array<LEX_STRING*> db_names; - COND *partial_cond= 0; + Item *partial_cond= plan->partial_cond; int error= 1; Open_tables_backup open_tables_state_backup; #ifndef NO_EMBEDDED_ACCESS_CHECKS Security_context *sctx= thd->security_ctx; #endif - uint table_open_method; + uint table_open_method= tables->table_open_method; bool can_deadlock; DBUG_ENTER("get_all_tables"); @@ -4725,9 +4694,6 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond) thd->reset_n_backup_open_tables_state(&open_tables_state_backup); schema_table_idx= get_schema_table_idx(schema_table); - tables->table_open_method= table_open_method= - get_table_open_method(tables, schema_table, schema_table_idx); - DBUG_PRINT("open_method", ("%d", tables->table_open_method)); /* this branch processes SHOW FIELDS, SHOW INDEXES commands. see sql_parse.cc, prepare_schema_table() function where @@ -4751,44 +4717,12 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond) goto err; } - if (get_lookup_field_values(thd, cond, tables, &lookup_field_vals)) + if (plan->no_rows) { error= 0; goto err; } - DBUG_PRINT("info",("db_name='%s', table_name='%s'", - lookup_field_vals.db_value.str, - lookup_field_vals.table_value.str)); - - if (!lookup_field_vals.wild_db_value && !lookup_field_vals.wild_table_value) - { - /* - if lookup value is empty string then - it's impossible table name or db name - */ - if ((lookup_field_vals.db_value.str && - !lookup_field_vals.db_value.str[0]) || - (lookup_field_vals.table_value.str && - !lookup_field_vals.table_value.str[0])) - { - error= 0; - goto err; - } - } - - if (lookup_field_vals.db_value.length && - !lookup_field_vals.wild_db_value) - tables->has_db_lookup_value= TRUE; - if (lookup_field_vals.table_value.length && - !lookup_field_vals.wild_table_value) - tables->has_table_lookup_value= TRUE; - - if (tables->has_db_lookup_value && tables->has_table_lookup_value) - partial_cond= 0; - else - partial_cond= make_cond_for_info_schema(cond, tables); - if (lex->describe) { /* EXPLAIN SELECT */ @@ -4798,7 +4732,7 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond) bzero((char*) &table_acl_check, sizeof(table_acl_check)); - if (make_db_list(thd, &db_names, &lookup_field_vals)) + if (make_db_list(thd, &db_names, &plan->lookup_field_vals)) goto err; for (size_t i=0; i < db_names.elements(); i++) { @@ -4814,7 +4748,7 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond) { Dynamic_array<LEX_STRING*> table_names; int res= make_table_name_list(thd, &table_names, lex, - &lookup_field_vals, db_name); + &plan->lookup_field_vals, db_name); if (res == 2) /* Not fatal error, continue */ continue; if (res) @@ -4852,8 +4786,8 @@ int get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond) already created by make_table_name_list() function). */ if (!table_open_method && schema_table_idx == SCH_TABLES && - (!lookup_field_vals.table_value.length || - lookup_field_vals.wild_table_value)) + (!plan->lookup_field_vals.table_value.length || + plan->lookup_field_vals.wild_table_value)) { table->field[0]->store(STRING_WITH_LEN("def"), system_charset_info); if (schema_table_store_record(thd, table)) @@ -8031,6 +7965,137 @@ int make_schema_select(THD *thd, SELECT_LEX *sel, /* + Optimize reading from an I_S table. + + @detail + This function prepares a plan for populating an I_S table with + get_all_tables(). + + The plan is in IS_table_read_plan structure, it is saved in + tables->is_table_read_plan. + + @return + false - Ok + true - Out Of Memory + +*/ + +static bool optimize_for_get_all_tables(THD *thd, TABLE_LIST *tables, COND *cond) +{ + SELECT_LEX *lsel= tables->schema_select_lex; + ST_SCHEMA_TABLE *schema_table= tables->schema_table; + enum enum_schema_tables schema_table_idx; + IS_table_read_plan *plan; + DBUG_ENTER("get_all_tables"); + + if (!(plan= new IS_table_read_plan())) + DBUG_RETURN(1); + + tables->is_table_read_plan= plan; + + schema_table_idx= get_schema_table_idx(schema_table); + tables->table_open_method= get_table_open_method(tables, schema_table, + schema_table_idx); + DBUG_PRINT("open_method", ("%d", tables->table_open_method)); + + /* + this branch processes SHOW FIELDS, SHOW INDEXES commands. + see sql_parse.cc, prepare_schema_table() function where + this values are initialized + */ + if (lsel && lsel->table_list.first) + { + /* These do not need to have a query plan */ + goto end; + } + + if (get_lookup_field_values(thd, cond, tables, &plan->lookup_field_vals)) + { + plan->no_rows= true; + goto end; + } + + DBUG_PRINT("info",("db_name='%s', table_name='%s'", + plan->lookup_field_vals.db_value.str, + plan->lookup_field_vals.table_value.str)); + + if (!plan->lookup_field_vals.wild_db_value && + !plan->lookup_field_vals.wild_table_value) + { + /* + if lookup value is empty string then + it's impossible table name or db name + */ + if ((plan->lookup_field_vals.db_value.str && + !plan->lookup_field_vals.db_value.str[0]) || + (plan->lookup_field_vals.table_value.str && + !plan->lookup_field_vals.table_value.str[0])) + { + plan->no_rows= true; + goto end; + } + } + + if (plan->has_db_lookup_value() && plan->has_table_lookup_value()) + plan->partial_cond= 0; + else + plan->partial_cond= make_cond_for_info_schema(cond, tables); + +end: + DBUG_RETURN(0); +} + + +/* + This is the optimizer part of get_schema_tables_result(). +*/ + +bool optimize_schema_tables_reads(JOIN *join) +{ + THD *thd= join->thd; + bool result= 0; + DBUG_ENTER("optimize_schema_tables_reads"); + + for (JOIN_TAB *tab= first_linear_tab(join, WITH_CONST_TABLES); + tab; + tab= next_linear_tab(join, tab, WITH_BUSH_ROOTS)) + { + if (!tab->table || !tab->table->pos_in_table_list) + continue; + + TABLE_LIST *table_list= tab->table->pos_in_table_list; + if (table_list->schema_table && thd->fill_information_schema_tables()) + { + /* A value of 0 indicates a dummy implementation */ + if (table_list->schema_table->fill_table == 0) + continue; + + /* skip I_S optimizations specific to get_all_tables */ + if (table_list->schema_table->fill_table != get_all_tables) + continue; + + Item *cond= tab->select_cond; + if (tab->cache_select && tab->cache_select->cond) + { + /* + If join buffering is used, we should use the condition that is + attached to the join cache. Cache condition has a part of WHERE that + can be checked when we're populating this table. + join_tab->select_cond is of no interest, because it only has + conditions that depend on both this table and previous tables in the + join order. + */ + cond= tab->cache_select->cond; + } + + optimize_for_get_all_tables(thd, table_list, cond); + } + } + DBUG_RETURN(result); +} + + +/* Fill temporary schema tables before SELECT SYNOPSIS @@ -8038,6 +8103,10 @@ int make_schema_select(THD *thd, SELECT_LEX *sel, join join which use schema tables executed_place place where I_S table processed + SEE ALSO + The optimization part is done by get_schema_tables_result(). This function + is run on query execution. + RETURN FALSE success TRUE error @@ -8058,7 +8127,7 @@ bool get_schema_tables_result(JOIN *join, for (JOIN_TAB *tab= first_linear_tab(join, WITH_CONST_TABLES); tab; - tab= next_linear_tab(join, tab, WITHOUT_BUSH_ROOTS)) + tab= next_linear_tab(join, tab, WITH_BUSH_ROOTS)) { if (!tab->table || !tab->table->pos_in_table_list) break; diff --git a/sql/sql_show.h b/sql/sql_show.h index 708a77d74cd..2f1cb26d17a 100644 --- a/sql/sql_show.h +++ b/sql/sql_show.h @@ -150,6 +150,69 @@ public: void call_in_target_thread(); }; +/** + Condition pushdown used for INFORMATION_SCHEMA / SHOW queries. + This structure is to implement an optimization when + accessing data dictionary data in the INFORMATION_SCHEMA + or SHOW commands. + When the query contain a TABLE_SCHEMA or TABLE_NAME clause, + narrow the search for data based on the constraints given. +*/ +typedef struct st_lookup_field_values +{ + /** + Value of a TABLE_SCHEMA clause. + Note that this value length may exceed @c NAME_LEN. + @sa wild_db_value + */ + LEX_STRING db_value; + /** + Value of a TABLE_NAME clause. + Note that this value length may exceed @c NAME_LEN. + @sa wild_table_value + */ + LEX_STRING table_value; + /** + True when @c db_value is a LIKE clause, + false when @c db_value is an '=' clause. + */ + bool wild_db_value; + /** + True when @c table_value is a LIKE clause, + false when @c table_value is an '=' clause. + */ + bool wild_table_value; +} LOOKUP_FIELD_VALUES; + + +/* + INFORMATION_SCHEMA: Execution plan for get_all_tables() call +*/ + +class IS_table_read_plan : public Sql_alloc +{ +public: + IS_table_read_plan() : no_rows(false) {} + + bool no_rows; + + LOOKUP_FIELD_VALUES lookup_field_vals; + Item *partial_cond; + + bool has_db_lookup_value() + { + return (lookup_field_vals.db_value.length && + !lookup_field_vals.wild_db_value); + } + bool has_table_lookup_value() + { + return (lookup_field_vals.table_value.length && + !lookup_field_vals.wild_table_value); + } +}; + +bool optimize_schema_tables_reads(JOIN *join); + /* Handle the ignored database directories list for SHOW/I_S. */ bool ignore_db_dirs_init(); void ignore_db_dirs_free(); diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 1b808d333d8..b9277adb15a 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -277,6 +277,7 @@ int mysql_update(THD *thd, List<Item> all_fields; killed_state killed_status= NOT_KILLED; Update_plan query_plan(thd->mem_root); + Explain_update *explain; query_plan.index= MAX_KEY; query_plan.using_filesort= FALSE; DBUG_ENTER("mysql_update"); @@ -717,15 +718,16 @@ int mysql_update(THD *thd, if (table->file->ha_table_flags() & HA_PARTIAL_COLUMN_READ) table->prepare_for_position(); + explain= thd->lex->explain->get_upd_del_plan(); /* We can use compare_record() to optimize away updates if the table handler is returning all columns OR if if all updated columns are read */ can_compare_record= records_are_comparable(table); - while (!(error=info.read_record(&info)) && !thd->killed) { + explain->on_record_read(); if (table->vfield) update_virtual_fields(thd, table, table->triggers ? VCOL_UPDATE_ALL : @@ -736,6 +738,7 @@ int mysql_update(THD *thd, if (table->file->was_semi_consistent_read()) continue; /* repeat the read of the same row if it still exists */ + explain->on_record_after_where(); store_record(table,record[1]); if (fill_record_n_invoke_before_triggers(thd, table, fields, values, 0, TRG_EVENT_UPDATE)) @@ -993,7 +996,11 @@ int mysql_update(THD *thd, id= thd->arg_of_last_insert_id_function ? thd->first_successful_insert_id_in_prev_stmt : 0; - if (error < 0) + if (thd->lex->analyze_stmt) + { + error= thd->lex->explain->send_explain(thd); + } + else if (error < 0) { char buff[MYSQL_ERRMSG_SIZE]; my_snprintf(buff, sizeof(buff), ER(ER_UPDATE_INFO), (ulong) found, @@ -1563,7 +1570,7 @@ bool mysql_multi_update(THD *thd, (*result)->abort_result_set(); else { - if (thd->lex->describe) + if (thd->lex->describe || thd->lex->analyze_stmt) res= thd->lex->explain->send_explain(thd); } thd->abort_on_warning= 0; @@ -2502,11 +2509,14 @@ bool multi_update::send_eof() DBUG_RETURN(TRUE); } - id= thd->arg_of_last_insert_id_function ? + if (!thd->lex->analyze_stmt) + { + id= thd->arg_of_last_insert_id_function ? thd->first_successful_insert_id_in_prev_stmt : 0; - my_snprintf(buff, sizeof(buff), ER(ER_UPDATE_INFO), - (ulong) found, (ulong) updated, (ulong) thd->cuted_fields); - ::my_ok(thd, (thd->client_capabilities & CLIENT_FOUND_ROWS) ? found : updated, - id, buff); + my_snprintf(buff, sizeof(buff), ER(ER_UPDATE_INFO), + (ulong) found, (ulong) updated, (ulong) thd->cuted_fields); + ::my_ok(thd, (thd->client_capabilities & CLIENT_FOUND_ROWS) ? found : updated, + id, buff); + } DBUG_RETURN(FALSE); } diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index b4d02612c15..db569ad01f7 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -982,7 +982,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); This makes the code grep-able, and helps maintenance. */ - + %token ABORT_SYM /* INTERNAL (used in lex) */ %token ACCESSIBLE_SYM %token ACTION /* SQL-2003-N */ @@ -1804,6 +1804,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize); %type <dyncol_def_list> dyncall_create_list %type <NONE> + analyze_stmt_command query verb_clause create change select do drop insert replace insert2 insert_values update delete truncate rename show describe load alter optimize keycache preload flush @@ -1990,6 +1991,7 @@ verb_clause: statement: alter | analyze + | analyze_stmt_command | binlog_base64_event | call | change @@ -12766,6 +12768,13 @@ describe_command: | DESCRIBE ; +analyze_stmt_command: + ANALYZE_SYM explainable_command + { + Lex->analyze_stmt= true; + } + ; + opt_extended_describe: /* empty */ {} | EXTENDED_SYM { Lex->describe|= DESCRIBE_EXTENDED; } diff --git a/sql/table.h b/sql/table.h index 3ac75ec06e1..b57e9c7227d 100644 --- a/sql/table.h +++ b/sql/table.h @@ -1499,6 +1499,7 @@ typedef struct st_schema_table uint i_s_requested_object; /* the object we need to open(TABLE | VIEW) */ } ST_SCHEMA_TABLE; +class IS_table_read_plan; /* Types of derived tables. The ending part is a bitmap of phases that are @@ -2044,12 +2045,23 @@ struct TABLE_LIST /* TRUE <=> this table is a const one and was optimized away. */ bool optimized_away; + /* I_S: Flags to open_table (e.g. OPEN_TABLE_ONLY or OPEN_VIEW_ONLY) */ uint i_s_requested_object; - bool has_db_lookup_value; - bool has_table_lookup_value; + + /* + I_S: how to read the tables (SKIP_OPEN_TABLE/OPEN_FRM_ONLY/OPEN_FULL_TABLE) + */ uint table_open_method; + /* + I_S: where the schema table was filled + (this is a hack. The code should be able to figure out whether reading + from I_S should be done by create_sort_index() or by JOIN::exec.) + */ enum enum_schema_table_state schema_table_state; + /* Something like a "query plan" for reading INFORMATION_SCHEMA table */ + IS_table_read_plan *is_table_read_plan; + MDL_request mdl_request; #ifdef WITH_PARTITION_STORAGE_ENGINE |