[Commits] Rev 3204: Fixed bug mdev-4894. in file:///home/igor/maria/maria-5.1/

Igor Babaev igor at askmonty.org
Wed Aug 14 01:21:12 EEST 2013


At file:///home/igor/maria/maria-5.1/

------------------------------------------------------------
revno: 3204
revision-id: igor at askmonty.org-20130813222111-vf6dg00gzb0gwipl
parent: holyfoot at askmonty.org-20130721195506-iwr7ydux2eksbroy
committer: Igor Babaev <igor at askmonty.org>
branch nick: maria-5.1
timestamp: Tue 2013-08-13 15:21:11 -0700
message:
  Fixed bug mdev-4894.
  This a an old legacy performance bug.
  When a very selective range scan existed for the second table in a join,
  and, at the same time, there was another range condition depending on the
  fields of the first table, the optimizer chose a plan with
  'Range checked for each record'. This plan was extremely inefficient in
  comparison with the regular selective range scan.
  As a matter of fact the range scan chosen for each record was the same as
  that selective range scan. 
  
  Changed the test case for bug 24776 to preserve the old output for explain.
   
-------------- next part --------------
=== modified file 'mysql-test/r/range.result'
--- a/mysql-test/r/range.result	2012-04-05 08:49:38 +0000
+++ b/mysql-test/r/range.result	2013-08-13 22:21:11 +0000
@@ -700,14 +700,14 @@
 'd8c4177d09f8b11f5.52725521');
 EXPLAIN
 SELECT s.oxid FROM t1 v, t1 s 
-WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
+WHERE 
 v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
 s.oxleft > v.oxleft AND s.oxleft < v.oxright;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	v	ref	OXLEFT,OXRIGHT,OXROOTID	OXROOTID	34	const	5	Using where
 1	SIMPLE	s	ALL	OXLEFT	NULL	NULL	NULL	6	Range checked for each record (index map: 0x4)
 SELECT s.oxid FROM t1 v, t1 s 
-WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
+WHERE
 v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
 s.oxleft > v.oxleft AND s.oxleft < v.oxright;
 oxid
@@ -1884,4 +1884,44 @@
 AAAA	AAAA	AAAA
 AAAAA	AAAAA	AAAAA
 DROP TABLE t1;
+#
+# mdev-4894:  Poor performance with unnecessary 
+# (bug#70021) 'Range checked for each record'
+#
+create table t1( key1 int not null, INDEX i1(key1) );
+insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8);
+insert into t1 select key1+8 from t1;
+insert into t1 select key1+16 from t1;
+insert into t1 select key1+32 from t1;
+insert into t1 select key1+64 from t1;
+insert into t1 select key1+128 from t1;
+insert into t1 select key1+256 from t1;
+insert into t1 select key1+512 from t1;
+alter table t1 add key2 int not null, add index i2(key2);
+update t1 set key2=key1;
+analyze table t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+create table t2 (a int);
+insert into t2 values (1),(2),(3),(4),(5),(6),(7),(8);
+insert into t2 select a+16 from t2;
+insert into t2 select a+32 from t2;
+insert into t2 select a+64 from t2;
+explain
+select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	64	
+1	SIMPLE	t1	range	i1,i2	i1	4	NULL	78	Using where
+select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000;
+count(*)
+128
+explain
+select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	64	
+1	SIMPLE	t1	range	i1,i2	i1	4	NULL	78	Using where
+select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a;
+count(*)
+126
+drop table t1,t2;
 End of 5.1 tests

=== modified file 'mysql-test/t/range.test'
--- a/mysql-test/t/range.test	2012-04-05 08:49:38 +0000
+++ b/mysql-test/t/range.test	2013-08-13 22:21:11 +0000
@@ -557,12 +557,12 @@
 
 EXPLAIN
 SELECT s.oxid FROM t1 v, t1 s 
-  WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
+  WHERE 
         v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
         s.oxleft > v.oxleft AND s.oxleft < v.oxright;
 
 SELECT s.oxid FROM t1 v, t1 s 
-  WHERE s.oxrootid = 'd8c4177d09f8b11f5.52725521' AND
+  WHERE
         v.oxrootid ='d8c4177d09f8b11f5.52725521' AND
         s.oxleft > v.oxleft AND s.oxleft < v.oxright;
 
@@ -1468,4 +1468,38 @@
 
 DROP TABLE t1;
 
+--echo #
+--echo # mdev-4894:  Poor performance with unnecessary 
+--echo # (bug#70021) 'Range checked for each record'
+--echo #
+
+create table t1( key1 int not null, INDEX i1(key1) );
+insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8);
+insert into t1 select key1+8 from t1; 
+insert into t1 select key1+16 from t1; 
+insert into t1 select key1+32 from t1; 
+insert into t1 select key1+64 from t1; 
+insert into t1 select key1+128 from t1; 
+insert into t1 select key1+256 from t1; 
+insert into t1 select key1+512 from t1; 
+
+alter table t1 add key2 int not null, add index i2(key2);
+update t1 set key2=key1;
+analyze table t1;
+
+create table t2 (a int);
+insert into t2 values (1),(2),(3),(4),(5),(6),(7),(8);
+insert into t2 select a+16 from t2;
+insert into t2 select a+32 from t2;
+insert into t2 select a+64 from t2;
+
+explain
+select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000;
+select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < 1000;
+explain
+select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a;
+select count(*) from t2 left join t1 on (t1.key1 < 3 or t1.key1 > 1020) and t1.key2 < t2.a;
+
+drop table t1,t2;
+
 --echo End of 5.1 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2013-05-11 12:55:11 +0000
+++ b/sql/sql_select.cc	2013-08-13 22:21:11 +0000
@@ -6605,19 +6605,18 @@
 	  else
 	  {
 	    sel->needed_reg=tab->needed_reg;
-	    sel->quick_keys.clear_all();
 	  }
+	  sel->quick_keys= tab->table->quick_keys;
 	  if (!sel->quick_keys.is_subset(tab->checked_keys) ||
               !sel->needed_reg.is_subset(tab->checked_keys))
 	  {
-	    tab->keys=sel->quick_keys;
-            tab->keys.merge(sel->needed_reg);
 	    tab->use_quick= (!sel->needed_reg.is_clear_all() &&
-			     (select->quick_keys.is_clear_all() ||
-			      (select->quick &&
-			       (select->quick->records >= 100L)))) ?
+			     (sel->quick_keys.is_clear_all() ||
+			      (sel->quick &&
+			       (sel->quick->records >= 100L)))) ?
 	      2 : 1;
 	    sel->read_tables= used_tables & ~current_map;
+            sel->quick_keys.clear_all();
 	  }
 	  if (i != join->const_tables && tab->use_quick != 2)
 	  {					/* Read with cache */



More information about the commits mailing list