From: Jorgen Loland Date: October 30 2012 9:32am Subject: bzr push into mysql-trunk branch (jorgen.loland:4851 to 4852) Bug#11754168 List-Archive: http://lists.mysql.com/commits/145301 X-Bug: 11754168 Message-Id: <20121030093211.16272.96582.4852@atum21.no.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 4852 Jorgen Loland 2012-10-30 Bug#11754168 - PARTS OF INDEX_MERGE_INNODB.TEST ARE DISABLED DUE TO EXPLAIN DIFFS When a huge amount of rows are updated, InnoDB's #records estimates go crazy for a while because indexes are full of delete-marked rows. InnoDB does not understand that delete-marked rows are deleted so they are counted. To improve testing, the CREATE TABLE - INSERT sequence is modified to not update rows. The result is that a bunch of tests now use index_merge where table scan used to be performed. There are no differences to the tests other than the way the tables are created and populated. modified: mysql-test/include/index_merge1.inc mysql-test/r/index_merge_innodb.result mysql-test/r/index_merge_myisam.result 4851 Sergey Glukhov 2012-10-30 [merge] null merge from 5.6 === modified file 'mysql-test/include/index_merge1.inc' --- a/mysql-test/include/index_merge1.inc 2012-06-28 12:46:30 +0000 +++ b/mysql-test/include/index_merge1.inc 2012-10-30 09:31:31 +0000 @@ -29,31 +29,37 @@ drop table if exists t0, t1, t2, t3, t4; create table t0 ( key1 int not null, - INDEX i1(key1) + key2 int not null, + key3 int not null, + key4 int not null, + key5 int not null, + key6 int not null, + key7 int not null, + key8 int not null, + INDEX i1(key1), + INDEX i2(key2), + INDEX i3(key3), + INDEX i4(key4), + INDEX i5(key5), + INDEX i6(key6), + INDEX i7(key7), + INDEX i8(key8) ); --disable_query_log -insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8); +insert into t0 values (1,1,1,1,1,1,1,1023),(2,2,2,2,2,2,2,1022); -let $1=7; -set @d=8; +let $1=9; +set @d=2; while ($1) { - eval insert into t0 select key1+@d from t0; + eval insert into t0 select key1+@d, key2+@d, key3+@d, key4+@d, key5+@d, + key6+@d, key7+@d, key8-@d from t0; eval set @d=@d*2; dec $1; } --enable_query_log -alter table t0 add key2 int not null, add index i2(key2); -alter table t0 add key3 int not null, add index i3(key3); -alter table t0 add key4 int not null, add index i4(key4); -alter table t0 add key5 int not null, add index i5(key5); -alter table t0 add key6 int not null, add index i6(key6); -alter table t0 add key7 int not null, add index i7(key7); -alter table t0 add key8 int not null, add index i8(key8); - -update t0 set key2=key1,key3=key1,key4=key1,key5=key1,key6=key1,key7=key1,key8=1024-key1; analyze table t0; # 1. One index === modified file 'mysql-test/r/index_merge_innodb.result' --- a/mysql-test/r/index_merge_innodb.result 2012-10-30 08:00:36 +0000 +++ b/mysql-test/r/index_merge_innodb.result 2012-10-30 09:31:31 +0000 @@ -4,16 +4,22 @@ drop table if exists t0, t1, t2, t3, t4; create table t0 ( key1 int not null, -INDEX i1(key1) +key2 int not null, +key3 int not null, +key4 int not null, +key5 int not null, +key6 int not null, +key7 int not null, +key8 int not null, +INDEX i1(key1), +INDEX i2(key2), +INDEX i3(key3), +INDEX i4(key4), +INDEX i5(key5), +INDEX i6(key6), +INDEX i7(key7), +INDEX i8(key8) ); -alter table t0 add key2 int not null, add index i2(key2); -alter table t0 add key3 int not null, add index i3(key3); -alter table t0 add key4 int not null, add index i4(key4); -alter table t0 add key5 int not null, add index i5(key5); -alter table t0 add key6 int not null, add index i6(key6); -alter table t0 add key7 int not null, add index i7(key7); -alter table t0 add key8 int not null, add index i8(key8); -update t0 set key2=key1,key3=key1,key4=key1,key5=key1,key6=key1,key7=key1,key8=1024-key1; analyze table t0; Table Op Msg_type Msg_text test.t0 analyze status OK @@ -34,7 +40,7 @@ key1 key2 key3 key4 key5 key6 key7 key8 1024 1024 1024 1024 1024 1024 1024 0 explain select * from t0 where key1 < 2 or key2 <3; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ALL i1,i2 NULL NULL NULL # Using where +1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where explain select * from t0 where (key1 > 30 and key1<35) or (key2 >32 and key2 < 40); id select_type table type possible_keys key key_len ref rows Extra @@ -58,7 +64,7 @@ id select_type table type possible_keys 1 SIMPLE t0 ref i1,i2,i3 i3 4 const # Using where explain select * from t0 use index (i1,i2) where (key1 < 2 or key2 <3) and key3 = 50; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ALL i1,i2 NULL NULL NULL # Using where +1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where explain select * from t0 where (key1 > 1 or key2 > 2); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 ALL i1,i2 NULL NULL NULL # Using where @@ -69,7 +75,7 @@ explain select * from t0 where key1<2 or key2<3 or (key1>5 and key1<7) or (key1>10 and key1<12) or (key2>100 and key2<102); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ALL i1,i2 NULL NULL NULL # Using where +1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where explain select * from t0 where key2 = 45 or key1 <=> null; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t0 range i1,i2 i2 4 NULL # Using where @@ -88,7 +94,7 @@ id select_type table type possible_keys explain select key1 from t0 where (key1 <=> null) or (key2 < 2) or (key3=10) or (key4 <=> null); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ALL i1,i2,i3,i4 NULL NULL NULL # Using where +1 SIMPLE t0 index_merge i1,i2,i3,i4 i2,i3 4,4 NULL # Using sort_union(i2,i3); Using where explain select key1 from t0 where (key1 <=> null) or (key1 < 5) or (key3=10) or (key4 <=> null); id select_type table type possible_keys key key_len ref rows Extra @@ -96,11 +102,11 @@ id select_type table type possible_keys explain select * from t0 where (key1 < 2 or key2 < 2) and (key3 < 3 or key4 < 3) and (key5 < 5 or key6 < 5); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ALL i1,i2,i3,i4,i5,i6 NULL NULL NULL # Using where +1 SIMPLE t0 index_merge i1,i2,i3,i4,i5,i6 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where explain select * from t0 where (key1 < 2 or key2 < 4) and (key1 < 5 or key3 < 3); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ALL i1,i2,i3 NULL NULL NULL # Using where +1 SIMPLE t0 index_merge i1,i2,i3 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where select * from t0 where (key1 < 2 or key2 < 4) and (key1 < 5 or key3 < 3); key1 key2 key3 key4 key5 key6 key7 key8 1 1 1 1 1 1 1 1023 @@ -109,15 +115,15 @@ key1 key2 key3 key4 key5 key6 key7 key8 explain select * from t0 where (key1 < 3 or key2 < 2) and (key3 < 3 or key4 < 3) and (key5 < 2 or key6 < 2); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ALL i1,i2,i3,i4,i5,i6 NULL NULL NULL # Using where +1 SIMPLE t0 index_merge i1,i2,i3,i4,i5,i6 i5,i6 4,4 NULL # Using sort_union(i5,i6); Using where explain select * from t0 where (key1 < 3 or key2 < 3) and (key3 < 70); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ALL i1,i2,i3 NULL NULL NULL # Using where +1 SIMPLE t0 index_merge i1,i2,i3 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where explain select * from t0 where (key1 < 3 or key2 < 3) and (key3 < 1000); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ALL i1,i2,i3 NULL NULL NULL # Using where +1 SIMPLE t0 index_merge i1,i2,i3 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where explain select * from t0 where ((key1 < 3 or key2 < 3) and (key2 <4 or key3 < 3)) or @@ -129,7 +135,7 @@ explain select * from t0 where or key1 < 5; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ALL i1,i2,i3 NULL NULL NULL # Using where +1 SIMPLE t0 index_merge i1,i2,i3 i1,i2 4,4 NULL # Using sort_union(i1,i2); Using where select * from t0 where ((key1 < 4 or key2 < 4) and (key2 <4 or key3 < 3)) or @@ -144,25 +150,25 @@ explain select * from t0 where or ((key5 < 3 or key6 < 3) and (key7 <3 or key8 < 3)); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ALL i1,i2,i3,i5,i6,i7,i8 NULL NULL NULL # Using where +1 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7,i8 i1,i2,i5,i6 4,4,4,4 NULL # Using sort_union(i1,i2,i5,i6); Using where explain select * from t0 where ((key3 <3 or key5 < 4) and (key1 < 3 or key2 < 3)) or ((key7 <5 or key8 < 3) and (key5 < 4 or key6 < 4)); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ALL i1,i2,i3,i5,i6,i7,i8 NULL NULL NULL # Using where +1 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7,i8 i3,i5,i7,i8 4,4,4,4 NULL # Using sort_union(i3,i5,i7,i8); Using where explain select * from t0 where ((key3 <3 or key5 < 4) and (key1 < 3 or key2 < 4)) or ((key3 <4 or key5 < 2) and (key5 < 5 or key6 < 3)); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ALL i1,i2,i3,i5,i6 NULL NULL NULL # Using where +1 SIMPLE t0 index_merge i1,i2,i3,i5,i6 i3,i5 4,4 NULL # Using sort_union(i3,i5); Using where explain select * from t0 where ((key3 <4 or key5 < 3) and (key1 < 3 or key2 < 3)) or (((key3 <5 and key7 < 5) or key5 < 2) and (key5 < 4 or key6 < 4)); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ALL i1,i2,i3,i5,i6,i7 NULL NULL NULL # Using where +1 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7 i3,i5 4,4 NULL # Using sort_union(i3,i5); Using where explain select * from t0 where ((key3 <5 or key5 < 4) and (key1 < 4 or key2 < 4)) or @@ -190,7 +196,7 @@ key1 key2 key3 key4 key5 key6 key7 key8 explain select * from t0 where key1 < 3 or key8 < 2 order by key1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 ALL i1,i8 NULL NULL NULL # Using where; Using filesort +1 SIMPLE t0 index_merge i1,i8 i1,i8 4,4 NULL # Using sort_union(i1,i8); Using where; Using filesort create table t2 like t0; insert into t2 select * from t0; alter table t2 add index i1_3(key1, key3); === modified file 'mysql-test/r/index_merge_myisam.result' --- a/mysql-test/r/index_merge_myisam.result 2012-10-30 08:00:36 +0000 +++ b/mysql-test/r/index_merge_myisam.result 2012-10-30 09:31:31 +0000 @@ -4,16 +4,22 @@ drop table if exists t0, t1, t2, t3, t4; create table t0 ( key1 int not null, -INDEX i1(key1) +key2 int not null, +key3 int not null, +key4 int not null, +key5 int not null, +key6 int not null, +key7 int not null, +key8 int not null, +INDEX i1(key1), +INDEX i2(key2), +INDEX i3(key3), +INDEX i4(key4), +INDEX i5(key5), +INDEX i6(key6), +INDEX i7(key7), +INDEX i8(key8) ); -alter table t0 add key2 int not null, add index i2(key2); -alter table t0 add key3 int not null, add index i3(key3); -alter table t0 add key4 int not null, add index i4(key4); -alter table t0 add key5 int not null, add index i5(key5); -alter table t0 add key6 int not null, add index i6(key6); -alter table t0 add key7 int not null, add index i7(key7); -alter table t0 add key8 int not null, add index i8(key8); -update t0 set key2=key1,key3=key1,key4=key1,key5=key1,key6=key1,key7=key1,key8=1024-key1; analyze table t0; Table Op Msg_type Msg_text test.t0 analyze status OK @@ -23,7 +29,7 @@ id select_type table type possible_keys explain select * from t0 where key1 < 3 or key2 > 1020; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 45 Using sort_union(i1,i2); Using where +1 SIMPLE t0 index_merge i1,i2 i1,i2 4,4 NULL 78 Using sort_union(i1,i2); Using where select * from t0 where key1 < 3 or key2 > 1020; key1 key2 key3 key4 key5 key6 key7 key8 1 1 1 1 1 1 1 1023 @@ -150,7 +156,7 @@ explain select * from t0 where or ((key7 <5 or key8 < 3) and (key5 < 4 or key6 < 4)); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7,i8 i3,i5,i7,i8 4,4,4,4 NULL 15 Using sort_union(i3,i5,i7,i8); Using where +1 SIMPLE t0 index_merge i1,i2,i3,i5,i6,i7,i8 i3,i5,i7,i8 4,4,4,4 NULL 16 Using sort_union(i3,i5,i7,i8); Using where explain select * from t0 where ((key3 <3 or key5 < 4) and (key1 < 3 or key2 < 4)) or @@ -190,7 +196,7 @@ key1 key2 key3 key4 key5 key6 key7 key8 explain select * from t0 where key1 < 3 or key8 < 2 order by key1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t0 index_merge i1,i8 i1,i8 4,4 NULL 5 Using sort_union(i1,i8); Using where; Using filesort +1 SIMPLE t0 index_merge i1,i8 i1,i8 4,4 NULL 6 Using sort_union(i1,i8); Using where; Using filesort create table t2 like t0; insert into t2 select * from t0; alter table t2 add index i1_3(key1, key3); No bundle (reason: useless for push emails).