MDEV-20804 Speed up main.index_merge_innodb

The test main.index_merge_innodb is taking very much time,
especially on later versions (10.2 and 10.3).

Some of this could be attributed to the use of INSERT...SELECT,
which is time-consumingly creating explicit record locks in InnoDB
for the locking read in the SELECT part.

In 10.3 and later, some slowness can be attributed to MDEV-12288,
which makes the InnoDB purge thread spend time to reset transaction
identifiers in the inserted records. If we prevent purge from
running before all tables are dropped, the test seems to be
10% faster on an unoptimized debug build on 10.5. (A proper fix
would be to implement MDEV-515 and stop writing row-level undo log
records for inserts into an empty table or partition.)

At the same time, it should not hurt to make main.index_merge_myisam
to use the sequence engine. Not only could it be a little faster,
but the test would be slightly more readable.

2 files changed, 25 insertions, 41 deletions

diff --git a/mysql-test/t/index_merge_innodb.test b/mysql-test/t/index_merge_innodb.test
index b7b2e60f20b..31ca1c253e4 100644
--- a/mysql-test/t/index_merge_innodb.test
+++ b/mysql-test/t/index_merge_innodb.test
@@ -14,7 +14,12 @@
 --source include/not_staging.inc
 --source include/have_xtradb.inc
 
-let $engine_type= InnoDB;
+connect disable_purge,localhost,root,,;
+--echo # Disable the purge of InnoDB history, to make the test run faster.
+START TRANSACTION WITH CONSISTENT SNAPSHOT;
+connection default;
+
+SET STORAGE_ENGINE = InnoDB;
 # InnoDB does not support Merge tables (affects include/index_merge1.inc)
 let $merge_table_support= 0;
 
@@ -37,7 +42,9 @@ set optimizer_switch='index_merge_sort_intersection=off';
 --echo # 
 --echo # BUG#56862/640419: Wrong result with sort_union index merge when one
 --echo #                   of the merged index scans is the primary key scan
---echo # 
+--echo #
+
+CREATE TABLE t0(a int, b int) ENGINE=MyISAM;
 
 CREATE TABLE t1 (
   pk int NOT NULL AUTO_INCREMENT PRIMARY KEY,
@@ -46,27 +53,19 @@ CREATE TABLE t1 (
   INDEX idx(a))
 ENGINE=INNODB;
 
-begin;
-INSERT INTO t1(a,b) VALUES
+INSERT INTO t0(a,b) VALUES
   (11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500),
   (3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800),
   (6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700),
   (13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000);
-INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1;
-INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1;
-INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
+INSERT INTO t0(a,b) SELECT a+20, b+2000 FROM t0;
+INSERT INTO t0(a,b) SELECT a+40, b+4000 FROM t0;
+INSERT INTO t0(a,b) SELECT a+80, b+8000 FROM t0;
+begin;
+INSERT INTO t1(a,b) SELECT t0.a,t0.b FROM t0, seq_1_to_1024;
 INSERT INTO t1 VALUES (1000000, 0, 0);
 commit;
+DROP TABLE t0;
 
 SET SESSION sort_buffer_size = 1024*36;
 set @tmp_optimizer_switch=@@optimizer_switch;
@@ -130,9 +129,6 @@ DROP TABLE t1;
 --echo # BUG#1006164: Multi-table DELETE that uses innodb + index_merge/intersect may fail to delete rows
 --echo #
 
-create table t0(a int);
-insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-
 create table t1 (
   pk int auto_increment, 
   zone_id int,
@@ -142,7 +138,7 @@ create table t1 (
   key (modified)
 ) engine=innodb;
 
-insert into t1 (zone_id, modified) select 0,0 from t0 A, t0 B, t0 C, t0 D;
+insert into t1 (zone_id, modified) select 0,0 from seq_1_to_10000;
 update t1 set zone_id=487, modified=9 where pk=7259;
 update t1 set zone_id=487, modified=9 where pk=7260;
 update t1 set zone_id=830, modified=9 where pk=8434;
@@ -156,7 +152,7 @@ DELETE t1 FROM t1 WHERE t1.zone_id=830 AND modified=9;
 commit;
 select * from t1 where t1.zone_id=830 AND modified=9;
 
-drop table t0, t1;
+drop table t1;
 
 --echo #
 --echo # MDEV-376: Wrong result (missing rows) with index_merge+index_merge_intersection, join
@@ -196,14 +192,10 @@ KEY key1 (key1),
 KEY key2 (key2)
 ) ENGINE=InnoDB AUTO_INCREMENT=12860259 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
 
-create table t2(a int);
-insert into t2 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
-
-create table t3(a int);
-insert into t3 select A.a + B.a* 10 + C.a * 100 + D.a*1000 from t2 A, t2 B, t2 C, t2 D;
-
 insert into t1 (key1, key2, col1,col2,col3,col4)
-select  a,a, a,a,a,a from t3;
+select seq,seq,seq,seq,seq,seq from seq_1_to_10000;
 SELECT sum(col1) FROM t1 FORCE INDEX (key1,key2) WHERE  (key1 between 10 and 8191+10) or (key2= 5);
-drop table t1,t2,t3;
+drop table t1;
 set optimizer_switch=@tmp_optimizer_switch;
+
+disconnect disable_purge;
diff --git a/mysql-test/t/index_merge_myisam.test b/mysql-test/t/index_merge_myisam.test
index 75beb9bd883..2cee768706b 100644
--- a/mysql-test/t/index_merge_myisam.test
+++ b/mysql-test/t/index_merge_myisam.test
@@ -10,7 +10,7 @@
 #               include/index_merge*.inc files
 #
 
-let $engine_type= MyISAM;
+SET STORAGE_ENGINE = MyISAM;
 # MyISAM supports Merge tables
 let $merge_table_support= 1;
 
@@ -33,7 +33,7 @@ insert into t1 select
   A.a * B.a*10 + C.a*100,
   A.a, 
   'filler'
-from t0 A, t0 B, t0 C; 
+from t0 A, t0 B, t0 C;
 
 --echo This should use union:
 explain select * from t1 where a=1 or b=1;
@@ -253,15 +253,7 @@ create table t0
   INDEX i1(key1)
 );
 
-insert into t0 values (1),(2),(3),(4),(5),(6),(7),(8);
-let $1=7;
-set @d=8;
-while ($1)
-{
-  eval insert into t0 select key1+ @d from t0;
-  eval set @d=@d*2;
-  dec $1;
-}
+insert into t0 select * from seq_1_to_1024;
 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 key8 int not null, add index i8(key8);