diff options
Diffstat (limited to 'mysql-test/main/show_analyze.result')
| -rw-r--r-- | mysql-test/main/show_analyze.result | 379 |
1 files changed, 379 insertions, 0 deletions
diff --git a/mysql-test/main/show_analyze.result b/mysql-test/main/show_analyze.result new file mode 100644 index 00000000000..8c37876fa50 --- /dev/null +++ b/mysql-test/main/show_analyze.result @@ -0,0 +1,379 @@ +drop table if exists t0, t1, t2, t3, t4; +drop view if exists v1; +SET @old_debug= @@session.debug; +set debug_sync='RESET'; +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int); +alter table t1 add b int, add c int, add filler char(32); +insert into t1 select A.a, 10*B.a, 100*C.a, 'foo filler' from t0 A, t0 B, t0 C; +alter table t1 add key(a), add key(b); +analyze table t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status Table is already up to date +show analyze for 1001; +ERROR HY000: Unknown thread id: 1001 +show analyze for (select a from t0 limit 1); +ERROR HY000: You may only use constant expressions in this statement +connect con1, localhost, root,,; +connection con1; +SET @old_debug= @@session.debug; +connection default; +show analyze for $thr2; +ERROR HY000: Target is not executing an operation with a query plan +show analyze for $thr1; +ERROR HY000: Target is not executing an operation with a query plan +connection con1; +set @show_explain_probe_select_id=1; +SET debug_dbug='+d,show_explain_probe_join_exec_end'; +select count(*) from t1 where c < 500; +connection default; +show analyze for $thr2; +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 1000 1000.00 59.38 50.00 Using where +Warnings: +Note 1003 select count(*) from t1 where c < 500 +connection con1; +count(*) +500 +select max(c) from t1 where c < 10; +connection default; +show analyze for $thr2; +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 1000 1000.00 10.16 10.00 Using where +Warnings: +Note 1003 select max(c) from t1 where c < 10 +connection con1; +max(c) +0 +# We can catch ANALYZE too. +analyze select max(c) from t1 where a < 10; +connection default; +show analyze for $thr2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL a NULL NULL NULL 1000 1000.00 99.90 100.00 Using where +Warnings: +Note 1003 analyze select max(c) from t1 where a < 10 +connection con1; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE t1 ALL a NULL NULL NULL 1000 1000.00 99.90 100.00 Using where +SET debug_dbug=@old_debug; +# UNION, select, first branch +set @show_explain_probe_select_id=1; +SET debug_dbug='+d,show_explain_probe_join_exec_end'; +select max(a) from t0 A where a<=5 union select max(a+1) from t0 B where a>=9; +connection default; +show analyze for $thr2; +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.00 100.00 60.00 Using where +2 UNION B ALL NULL NULL NULL NULL 10 NULL 100.00 NULL Using where +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 0.00 NULL NULL +Warnings: +Note 1003 select max(a) from t0 A where a<=5 union select max(a+1) from t0 B where a>=9 +connection con1; +max(a) +5 +10 +# UNION, select, second branch +set @show_explain_probe_select_id=2; +SET debug_dbug='+d,show_explain_probe_join_exec_end'; +select max(a) from t0 A where a<=5 union select max(a+1) from t0 B where a>=9; +connection default; +show analyze for $thr2; +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.00 100.00 60.00 Using where +2 UNION B ALL NULL NULL NULL NULL 10 10.00 100.00 10.00 Using where +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 0.00 NULL NULL +Warnings: +Note 1003 select max(a) from t0 A where a<=5 union select max(a+1) from t0 B where a>=9 +connection con1; +max(a) +5 +10 +# UNION, analyze, first branch +set @show_explain_probe_select_id=1; +SET debug_dbug='+d,show_explain_probe_join_exec_end'; +analyze select a from t0 A where a<=5 union select a+1 from t0 B where a>=9; +connection default; +show analyze for $thr2; +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.00 100.00 60.00 Using where +2 UNION B ALL NULL NULL NULL NULL 10 NULL 100.00 NULL Using where +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 0.00 NULL NULL +Warnings: +Note 1003 analyze select a from t0 A where a<=5 union select a+1 from t0 B where a>=9 +connection con1; +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.00 100.00 60.00 Using where +2 UNION B ALL NULL NULL NULL NULL 10 10.00 100.00 10.00 Using where +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 7.00 NULL NULL +# UNION, analyze, second branch +set @show_explain_probe_select_id=2; +analyze select a from t0 A where a<=5 union select a+1 from t0 B where a>=9; +connection default; +show analyze for $thr2; +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.00 100.00 60.00 Using where +2 UNION B ALL NULL NULL NULL NULL 10 10.00 100.00 10.00 Using where +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 0.00 NULL NULL +Warnings: +Note 1003 analyze select a from t0 A where a<=5 union select a+1 from t0 B where a>=9 +connection con1; +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.00 100.00 60.00 Using where +2 UNION B ALL NULL NULL NULL NULL 10 10.00 100.00 10.00 Using where +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 7.00 NULL NULL +SET debug_dbug=@old_debug; +# Uncorrelated subquery, select +set @show_explain_probe_select_id=1; +SET debug_dbug='+d,show_explain_probe_join_exec_end'; +select a, (select max(a) from t0 B where a>6) from t0 A where a<2; +connection default; +show analyze for $thr2; +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.00 100.00 20.00 Using where +2 SUBQUERY B ALL NULL NULL NULL NULL 10 10.00 100.00 30.00 Using where +Warnings: +Note 1003 select a, (select max(a) from t0 B where a>6) from t0 A where a<2 +connection con1; +a (select max(a) from t0 B where a>6) +0 9 +1 9 +SET debug_dbug=@old_debug; +# Uncorrelated subquery, analyze +set @show_explain_probe_select_id=1; +SET debug_dbug='+d,show_explain_probe_join_exec_end'; +analyze select a, (select max(a) from t0 B where a>6) from t0 A where a<2; +connection default; +show analyze for $thr2; +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.00 100.00 20.00 Using where +2 SUBQUERY B ALL NULL NULL NULL NULL 10 10.00 100.00 30.00 Using where +Warnings: +Note 1003 analyze select a, (select max(a) from t0 B where a>6) from t0 A where a<2 +connection con1; +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.00 100.00 20.00 Using where +2 SUBQUERY B ALL NULL NULL NULL NULL 10 10.00 100.00 30.00 Using where +SET debug_dbug=@old_debug; +# correlated subquery, select, before execution start +set @show_explain_probe_select_id=1; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; +select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<2; +connection default; +show analyze for $thr2; +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 NULL 100.00 NULL Using where +2 DEPENDENT SUBQUERY b ALL NULL NULL NULL NULL 10 NULL 100.00 NULL Using where +Warnings: +Note 1003 select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<2 +connection con1; +a (select max(a) from t0 b where b.a+a.a<10) +0 9 +1 8 +SET debug_dbug=@old_debug; +# correlated subquery, select, after execution +set @show_explain_probe_select_id=1; +SET debug_dbug='+d,show_explain_probe_join_exec_end'; +select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<2; +connection default; +show analyze for $thr2; +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.00 100.00 20.00 Using where +2 DEPENDENT SUBQUERY b ALL NULL NULL NULL NULL 10 10.00 100.00 95.00 Using where +Warnings: +Note 1003 select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<2 +connection con1; +a (select max(a) from t0 b where b.a+a.a<10) +0 9 +1 8 +SET debug_dbug=@old_debug; +# correlated subquery, analyze +set @show_explain_probe_select_id=1; +SET debug_dbug='+d,show_explain_probe_join_exec_end'; +analyze select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<2; +connection default; +show analyze for $thr2; +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.00 100.00 20.00 Using where +2 DEPENDENT SUBQUERY b ALL NULL NULL NULL NULL 10 10.00 100.00 95.00 Using where +Warnings: +Note 1003 analyze select a, (select max(a) from t0 b where b.a+a.a<10) from t0 a where a<2 +connection con1; +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.00 100.00 20.00 Using where +2 DEPENDENT SUBQUERY b ALL NULL NULL NULL NULL 10 10.00 100.00 95.00 Using where +SET debug_dbug=@old_debug; +# Try to do SHOW ANALYZE for a query that runs a SET command: +# +set @show_explain_probe_select_id=2; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; +set @foo= (select max(a) from t0 where sin(a) >0); +connection default; +show analyze for $thr2; +ERROR HY000: Target is not executing an operation with a query plan +kill query $thr2; +connection con1; +ERROR 70100: Query execution was interrupted +SET debug_dbug=@old_debug; +# +# Attempt SHOW ANALYZE for an UPDATE +# +create table t2 as select a as a, a as dummy from t0 limit 2; +set @show_explain_probe_select_id=2; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; +update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3 ; +connection default; +show analyze for $thr2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 1.00 100.00 0.00 Using where +2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 10 NULL 100.00 NULL Using where +Warnings: +Note 1003 update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3 +show analyze for $thr2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 2.00 100.00 0.00 Using where +2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 10 10.00 100.00 30.00 Using where +Warnings: +Note 1003 update t2 set dummy=0 where (select max(a) from t0 where t2.a + t0.a <3) >3 +connection con1; +drop table t2; +SET debug_dbug=@old_debug; +# +# Attempt SHOW ANALYZE for a DELETE +# +create table t2 as select a as a, a as dummy from t0 limit 2; +set @show_explain_probe_select_id=2; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; +delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3 ; +connection default; +show analyze for $thr2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 1.00 100.00 0.00 Using where +2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 10 NULL 100.00 NULL Using where +Warnings: +Note 1003 delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3 +show analyze for $thr2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 2.00 100.00 0.00 Using where +2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 10 10.00 100.00 30.00 Using where +Warnings: +Note 1003 delete from t2 where (select max(a) from t0 where t2.a + t0.a <3) >3 +connection con1; +drop table t2; +SET debug_dbug=@old_debug; +# +# Multiple SHOW ANALYZE calls for one select +# +create table t2 as select a as a, a as dummy from t0 limit 3; +set @show_explain_probe_select_id=2; +SET debug_dbug='+d,show_explain_probe_join_exec_start'; +select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2; +connection default; +show analyze for $thr2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 1.00 100.00 100.00 +2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 10 NULL 100.00 NULL Using where +Warnings: +Note 1003 select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2 +show analyze for $thr2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 2.00 100.00 100.00 +2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 10 10.00 100.00 30.00 Using where +Warnings: +Note 1003 select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2 +show analyze for $thr2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 3 3.00 100.00 100.00 +2 DEPENDENT SUBQUERY t0 ALL NULL NULL NULL NULL 10 10.00 100.00 25.00 Using where +Warnings: +Note 1003 select t2.a, ((select max(a) from t0 where t2.a + t0.a <3) >3) as SUBQ from t2 +connection con1; +a SUBQ +0 0 +1 0 +2 0 +SET debug_dbug=@old_debug; +drop table t2; +# +# SHOW ANALYZE for SELECT ... ORDER BY with "Using filesort" +# +SET debug_dbug='+d,show_explain_probe_join_exec_end'; +set @show_explain_probe_select_id=1; +select * from t0 order by a; +connection default; +show analyze for $thr2; +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.00 100.00 100.00 Using filesort +Warnings: +Note 1003 select * from t0 order by a +connection con1; +a +0 +1 +2 +3 +4 +5 +6 +7 +8 +9 +SET debug_dbug=@old_debug; +# +# SHOW ANALYZE for SELECT ... with "Using temporary" +# +connection con1; +SET debug_dbug='+d,show_explain_probe_join_exec_end'; +set @show_explain_probe_select_id=1; +select distinct a from t0; +connection default; +show analyze for $thr2; +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.00 100.00 100.00 Using temporary +Warnings: +Note 1003 select distinct a from t0 +connection con1; +a +0 +1 +2 +3 +4 +5 +6 +7 +8 +9 +SET debug_dbug=@old_debug; +# +# SHOW ANALYZE for SELECT ... with "Using temporary; Using filesort" +# +SET debug_dbug='+d,show_explain_probe_join_exec_end'; +set @show_explain_probe_select_id=1; +select distinct a from t0; +connection default; +show analyze for $thr2; +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.00 100.00 100.00 Using temporary +Warnings: +Note 1003 select distinct a from t0 +connection con1; +a +0 +1 +2 +3 +4 +5 +6 +7 +8 +9 +SET debug_dbug=@old_debug; +drop table t0,t1; +# End +connection default; +disconnect con1; +set debug_sync='RESET'; |