From: Date: May 7 2008 7:58am Subject: bk commit into 5.1 tree (sergefp:1.2613) BUG#35850 List-Archive: http://lists.mysql.com/commits/46438 X-Bug: 35850 Message-Id: <20080507055831.332E425ED02@pslp.localdomain> 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