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).
| Thread |
|---|
| • bzr push into mysql-trunk branch (jorgen.loland:4851 to 4852) Bug#11754168 | Jorgen Loland | 16 Nov |
