List:Commits« Previous MessageNext Message »
From:Sergey Petrunia Date:May 7 2008 5:58am
Subject:bk commit into 5.1 tree (sergefp:1.2613) BUG#35850
View as plain text  
Below is the list of changes that have just been committed into a local
5.1 repository of sergefp.  When sergefp does a push these changes
will be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2008-05-07 09:58:21+04:00, sergefp@stripped +5 -0
  BUG#35850 "Performance regression in 5.1.23/5.1.24"
  - Disable the "prefer full scan on clustered primary key over full scan
    of any secondary key" rule introduced by BUG#35850.
  - Update test results accordingly 
  (bk trigger: file this for BUG#35850)

  mysql-test/r/innodb.result@stripped, 2008-05-07 09:57:31+04:00, sergefp@stripped +15 -15
    BUG#35850 "Performance regression in 5.1.23/5.1.24"
    - Update test results

  mysql-test/r/innodb_mysql.result@stripped, 2008-05-07 09:57:31+04:00, sergefp@stripped +18 -8
    BUG#35850 "Performance regression in 5.1.23/5.1.24"
    - Testcase
    - Update test results

  mysql-test/r/join_outer_innodb.result@stripped, 2008-05-07 09:57:31+04:00, sergefp@stripped +2 -2
    BUG#35850 "Performance regression in 5.1.23/5.1.24"
    - Update test results

  mysql-test/t/innodb_mysql.test@stripped, 2008-05-07 09:57:31+04:00, sergefp@stripped +16 -0
    BUG#35850 "Performance regression in 5.1.23/5.1.24"
    - Testcase

  sql/sql_select.cc@stripped, 2008-05-07 09:57:32+04:00, sergefp@stripped +6 -3
    BUG#35850 "Performance regression in 5.1.23/5.1.24"
    - Disable the "prefer full scan on clustered primary key over full scan
      of any secondary key" rule introduced by BUG#35850.

diff -Nrup a/mysql-test/r/innodb.result b/mysql-test/r/innodb.result
--- a/mysql-test/r/innodb.result	2008-03-27 04:37:03 +03:00
+++ b/mysql-test/r/innodb.result	2008-05-07 09:57:31 +04:00
@@ -925,7 +925,7 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	index	NULL	b	4	NULL	#	Using index
 explain select a,b from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	#	
+1	SIMPLE	t1	index	NULL	b	4	NULL	#	Using index
 explain select a,b,c from t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	#	
@@ -1166,14 +1166,14 @@ UPDATE t1 set a=a+100 where b between 2 
 SELECT * from t1;
 a	b
 1	1
+102	2
+103	3
 4	4
 5	5
 6	6
 7	7
 8	8
 9	9
-102	2
-103	3
 drop table t1;
 CREATE TABLE t1 (a int not null primary key, b int not null, key (b)) engine=innodb;
 CREATE TABLE t2 (a int not null primary key, b int not null, key (b)) engine=innodb;
@@ -1197,6 +1197,7 @@ a	b
 update t1,t2 set t1.a=t1.a+100 where t1.a=101;
 select * from t1;
 a	b
+201	1
 102	2
 103	3
 104	4
@@ -1208,11 +1209,10 @@ a	b
 110	10
 111	11
 112	12
-201	1
 update t1,t2 set t1.b=t1.b+10 where t1.b=2;
 select * from t1;
 a	b
-102	12
+201	1
 103	3
 104	4
 105	5
@@ -1222,34 +1222,34 @@ a	b
 109	9
 110	10
 111	11
+102	12
 112	12
-201	1
 update t1,t2 set t1.b=t1.b+2,t2.b=t1.b+10 where t1.b between 3 and 5 and t1.a=t2.a+100;
 select * from t1;
 a	b
-102	12
+201	1
 103	5
 104	6
-105	7
 106	6
+105	7
 107	7
 108	8
 109	9
 110	10
 111	11
+102	12
 112	12
-201	1
 select * from t2;
 a	b
 1	1
 2	2
-3	13
-4	14
-5	15
 6	6
 7	7
 8	8
 9	9
+3	13
+4	14
+5	15
 drop table t1,t2;
 CREATE TABLE t2 (   NEXT_T         BIGINT NOT NULL PRIMARY KEY) ENGINE=MyISAM;
 CREATE TABLE t1 (  B_ID           INTEGER NOT NULL PRIMARY KEY) ENGINE=InnoDB;
@@ -1300,11 +1300,11 @@ insert into t1 (id) values (null),(null)
 update t1 set fk=69 where fk is null order by id limit 1;
 SELECT * from t1;
 id	fk
-1	69
 2	NULL
 3	NULL
 4	NULL
 5	NULL
+1	69
 drop table t1;
 create table t1 (a int not null, b int not null, key (a));
 insert into t1 values (1,1),(1,2),(1,3),(3,1),(3,2),(3,3),(3,1),(3,2),(3,3),(2,1),(2,2),(2,3);
@@ -2438,8 +2438,8 @@ insert into t1 (b) values (1);
 replace into t1 (b) values (2), (1), (3);
 select * from t1;
 a	b
-2	2
 3	1
+2	2
 4	3
 truncate table t1;
 insert into t1 (b) values (1);
@@ -2448,8 +2448,8 @@ replace into t1 (b) values (1);
 replace into t1 (b) values (3);
 select * from t1;
 a	b
-2	2
 3	1
+2	2
 4	3
 drop table t1;
 create table t1 (rowid int not null auto_increment, val int not null,primary
diff -Nrup a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
--- a/mysql-test/r/innodb_mysql.result	2008-04-29 07:03:13 +04:00
+++ b/mysql-test/r/innodb_mysql.result	2008-05-07 09:57:31 +04:00
@@ -355,13 +355,13 @@ EXPLAIN
 SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
 WHERE t1.name LIKE 'A%';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	PRIMARY,name	PRIMARY	4	NULL	3	Using where
+1	SIMPLE	t1	index	PRIMARY,name	name	23	NULL	3	Using where; Using index
 1	SIMPLE	t2	ref	fkey	fkey	5	test.t1.id	1	Using where; Using index
 EXPLAIN
 SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id
 WHERE t1.name LIKE 'A%' OR FALSE;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	index	NULL	PRIMARY	4	NULL	5	
+1	SIMPLE	t2	index	NULL	fkey	5	NULL	5	Using index
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.fkey	1	Using where
 DROP TABLE t1,t2;
 CREATE TABLE t1 (
@@ -1268,11 +1268,11 @@ select_type	SIMPLE
 table	t1
 type	index
 possible_keys	NULL
-key	PRIMARY
-key_len	4
+key	b
+key_len	5
 ref	NULL
 rows	3
-Extra	Using filesort
+Extra	Using index; Using filesort
 SELECT * FROM t1 ORDER BY b ASC, a DESC;
 a	b
 1	1
@@ -1284,11 +1284,11 @@ select_type	SIMPLE
 table	t1
 type	index
 possible_keys	NULL
-key	PRIMARY
-key_len	4
+key	b
+key_len	5
 ref	NULL
 rows	3
-Extra	Using filesort
+Extra	Using index; Using filesort
 SELECT * FROM t1 ORDER BY b DESC, a ASC;
 a	b
 2	2
@@ -1654,3 +1654,13 @@ ALTER TABLE t1 CHANGE id id2 INT;
 DROP TABLE t2;
 DROP TABLE t1;
 End of 5.1 tests
+drop table if exists t1, t2, t3;
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (a int, b int, pk int, key(a,b), primary key(pk)) engine=innodb;
+insert into t2 select @a:=A.a+10*(B.a + 10*C.a),@a, @a from t1 A, t1 B, t1 C;
+this must use key 'a', not PRIMARY:
+explain select a from t2 where a=b;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	index	NULL	a	10	NULL	#	Using where; Using index
+drop table t1, t2;
diff -Nrup a/mysql-test/r/join_outer_innodb.result b/mysql-test/r/join_outer_innodb.result
--- a/mysql-test/r/join_outer_innodb.result	2007-11-07 18:59:56 +03:00
+++ b/mysql-test/r/join_outer_innodb.result	2008-05-07 09:57:31 +04:00
@@ -8,12 +8,12 @@ EXPLAIN
 SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id 
 WHERE t1.name LIKE 'A%';
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	index	PRIMARY,name	PRIMARY	4	NULL	3	Using where
+1	SIMPLE	t1	index	PRIMARY,name	name	23	NULL	3	Using where; Using index
 1	SIMPLE	t2	ref	fkey	fkey	5	test.t1.id	1	Using where; Using index
 EXPLAIN
 SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id 
 WHERE t1.name LIKE 'A%' OR FALSE;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	index	NULL	PRIMARY	4	NULL	5	
+1	SIMPLE	t2	index	NULL	fkey	5	NULL	5	Using index
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.fkey	1	Using where
 DROP TABLE t1,t2;
diff -Nrup a/mysql-test/t/innodb_mysql.test b/mysql-test/t/innodb_mysql.test
--- a/mysql-test/t/innodb_mysql.test	2007-10-08 22:57:28 +04:00
+++ b/mysql-test/t/innodb_mysql.test	2008-05-07 09:57:31 +04:00
@@ -13,3 +13,19 @@ let $test_foreign_keys= 1;
 set global innodb_support_xa=default;
 set session innodb_support_xa=default;
 --source include/mix1.inc
+
+--disable_warnings
+drop table if exists t1, t2, t3;
+--enable_warnings
+#
+# BUG#35850: Performance regression in 5.1.23/5.1.24
+#
+create table t1(a int);
+insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t2 (a int, b int, pk int, key(a,b), primary key(pk)) engine=innodb;
+insert into t2 select @a:=A.a+10*(B.a + 10*C.a),@a, @a from t1 A, t1 B, t1 C;
+--echo this must use key 'a', not PRIMARY:
+--replace_column 9 #
+explain select a from t2 where a=b;
+drop table t1, t2;
+
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc	2008-04-25 01:19:38 +04:00
+++ b/sql/sql_select.cc	2008-05-07 09:57:32 +04:00
@@ -6506,13 +6506,16 @@ make_join_readinfo(JOIN *join, ulonglong
 		   !(tab->select && tab->select->quick))
 	  {					// Only read index tree
 	    /*
-	      See bug #26447: "Using the clustered index for a table scan
-	      is always faster than using a secondary index".
-	    */
+            It has turned out that the below change, while speeding things
+            up for disk-bound loads, slows them down for cases when the data
+            is in disk cache (see BUG#35850):
+	    //  See bug #26447: "Using the clustered index for a table scan
+	    //  is always faster than using a secondary index".
             if (table->s->primary_key != MAX_KEY &&
                 table->file->primary_key_is_clustered())
               tab->index= table->s->primary_key;
             else
+	    */
               tab->index=find_shortest_key(table, & table->covering_keys);
 	    tab->read_first_record= join_read_first;
 	    tab->type=JT_NEXT;		// Read with index_first / index_next
Thread
bk commit into 5.1 tree (sergefp:1.2613) BUG#35850Sergey Petrunia7 May