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#35850 | Sergey Petrunia | 7 May |
