[Commits] Rev 3500: EXPLAIN DELETE for MariaDB in file:///data0/psergey/dev2/10.0-base-explain-update-r2/

Sergey Petrunya psergey at askmonty.org
Tue Feb 12 06:24:49 EET 2013


At file:///data0/psergey/dev2/10.0-base-explain-update-r2/

------------------------------------------------------------
revno: 3500
revision-id: psergey at askmonty.org-20130212042448-zwl8hexailedt6pv
parent: psergey at askmonty.org-20130212042014-7i5i712qajnesbgc
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 10.0-base-explain-update-r2
timestamp: Tue 2013-02-12 08:24:48 +0400
message:
  EXPLAIN DELETE for MariaDB
  - Include the testcases in the backport.
=== added file 'mysql-test/r/explain_non_select.result'
--- a/mysql-test/r/explain_non_select.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/explain_non_select.result	2013-02-12 04:24:48 +0000
@@ -0,0 +1,53 @@
+drop table if exists t0;
+create table t0 (a int) engine=myisam;
+insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8);
+#
+#  Tests for single-table DELETE
+# 
+explain select * from t0 where a=3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	8	Using where
+explain delete from t0 where a=3;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	8	Using where
+# DELETE without WHERE is a special case:
+explain delete from t0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Deleting all rows
+create table t1 (a int, b int, filler char(100), key(a), key(b));
+insert into t1 
+select A.a+10*B.a + 10*C.a, A.a+10*B.a + 10*C.a, 'filler' 
+from t0 A, t0 B, t0 C;
+# This should use an index,  possible_keys=NULL because there is no WHERE
+explain delete from t1 order by a limit 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	index	NULL	a	NULL	NULL	512	
+# This should use range, possible_keys={a,b}
+explain delete from t1 where a<20 and b < 10;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a,b	a	5	NULL	1	Using where
+# This should use ALL + filesort
+explain delete from t1 order by a+1 limit 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	512	Using filesort
+# This should use range + using filesort
+explain delete from t1 where a<20 order by b limit 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	5	NULL	1	Using where; Using filesort
+# Try some subqueries:
+explain delete from t1 where a < (select max(a) from t0);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	range	a	a	5	NULL	1	Using where
+2	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Query plan already deleted
+explain delete from t1 where a < (select max(a) from t0 where a < t1.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	512	Using where
+2	DEPENDENT SUBQUERY	t0	ALL	NULL	NULL	NULL	NULL	8	Using where
+#
+#  Tests for multi-table DELETE
+# 
+explain delete t1 from t0, t1 where t0.a = t1.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	8	Using where
+1	SIMPLE	t1	ref	a	a	5	test.t0.a	4	Using index
+drop table t0, t1;

=== added file 'mysql-test/r/show_explain_non_select.result'
--- a/mysql-test/r/show_explain_non_select.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/show_explain_non_select.result	2013-02-12 04:24:48 +0000
@@ -0,0 +1,24 @@
+drop table if exists t0, t1;
+create table t0 (a int) engine=myisam;
+insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8);
+create table t1 (a int, b int, filler char(100), key(a), key(b));
+insert into t1 
+select A.a+10*B.a + 10*C.a, A.a+10*B.a + 10*C.a, 'filler' 
+from t0 A, t0 B, t0 C;
+set debug_dbug='d,show_explain_probe_delete_exec_start';
+delete from t1 where a<10 and b+1>1000;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	a	a	5	NULL	1	Using where
+Warnings:
+Note	1003	delete from t1 where a<10 and b+1>1000
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_do_select';
+delete t1 from t1, t0 where t0.a=t1.a and t1.b +1 > 1000;
+show explain for $thr2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t0	ALL	NULL	NULL	NULL	NULL	8	Using where
+1	SIMPLE	t1	ref	a	a	5	test.t0.a	4	Using where
+Warnings:
+Note	1003	delete t1 from t1, t0 where t0.a=t1.a and t1.b +1 > 1000
+drop table t0,t1;

=== added file 'mysql-test/t/explain_non_select.test'
--- a/mysql-test/t/explain_non_select.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/explain_non_select.test	2013-02-12 04:24:48 +0000
@@ -0,0 +1,47 @@
+#
+# MariaDB tests for EXPLAIN UPDATE/DELETE.
+#
+--disable_warnings
+drop table if exists t0;
+--enable_warnings
+
+create table t0 (a int) engine=myisam;
+insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8);
+
+--echo #
+--echo #  Tests for single-table DELETE
+--echo # 
+
+explain select * from t0 where a=3;
+explain delete from t0 where a=3;
+
+--echo # DELETE without WHERE is a special case:
+explain delete from t0;
+
+create table t1 (a int, b int, filler char(100), key(a), key(b));
+insert into t1 
+select A.a+10*B.a + 10*C.a, A.a+10*B.a + 10*C.a, 'filler' 
+from t0 A, t0 B, t0 C;
+
+--echo # This should use an index,  possible_keys=NULL because there is no WHERE
+explain delete from t1 order by a limit 2;
+
+--echo # This should use range, possible_keys={a,b}
+explain delete from t1 where a<20 and b < 10;
+
+--echo # This should use ALL + filesort
+explain delete from t1 order by a+1 limit 2;
+
+--echo # This should use range + using filesort
+explain delete from t1 where a<20 order by b limit 2;
+
+--echo # Try some subqueries:
+explain delete from t1 where a < (select max(a) from t0);
+explain delete from t1 where a < (select max(a) from t0 where a < t1.b);
+
+--echo #
+--echo #  Tests for multi-table DELETE
+--echo # 
+explain delete t1 from t0, t1 where t0.a = t1.a;
+
+drop table t0, t1;

=== added file 'mysql-test/t/show_explain_non_select.test'
--- a/mysql-test/t/show_explain_non_select.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/show_explain_non_select.test	2013-02-12 04:24:48 +0000
@@ -0,0 +1,57 @@
+#
+# SHOW EXPLAIN tests for non-select subqueries
+#
+#--source include/have_debug.inc
+#--source include/have_innodb.inc
+#--source include/not_embedded.inc
+
+--disable_warnings
+drop table if exists t0, t1;
+--enable_warnings
+
+# 
+# Setup two threads and their ids
+#
+let $thr1=`select connection_id()`;
+connect (con2, localhost, root,,);
+connection con2;
+let $thr2=`select connection_id()`;
+connection default;
+
+# 
+# Create tables
+# 
+create table t0 (a int) engine=myisam;
+insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8);
+
+create table t1 (a int, b int, filler char(100), key(a), key(b));
+insert into t1 
+select A.a+10*B.a + 10*C.a, A.a+10*B.a + 10*C.a, 'filler' 
+from t0 A, t0 B, t0 C;
+
+let $wait_condition= select State='show_explain_trap' from information_schema.processlist where id=$thr2;
+
+#
+# Test SHOW EXPLAIN for single-table DELETEs
+#
+connection con2;
+set debug_dbug='d,show_explain_probe_delete_exec_start';
+send delete from t1 where a<10 and b+1>1000;
+
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con2;
+reap;
+
+set @show_explain_probe_select_id=1;
+set debug_dbug='d,show_explain_probe_do_select';
+send delete t1 from t1, t0 where t0.a=t1.a and t1.b +1 > 1000;
+connection default;
+--source include/wait_condition.inc
+evalp show explain for $thr2;
+connection con2;
+reap;
+
+
+drop table t0,t1;



More information about the commits mailing list